![\"\"](\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2014\/10\/Restore-Failed.png\")
<\/a><\/p>\nThere are a couple of ways to handle forcing exclusive access on a database. First we have the ALTER DATABASE<\/a> method in T-SQL which is likely the most popular and most widely known.<\/p>\n ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE The ALTER DATABASE\u00a0method is not bullet-proof, however. For example, exclusive access is required on a database snapshot if you want\u00a0to restore another database from the snapshot. You cannot set a database snapshot to SINGLE_USER mode which makes gaining exclusive access harder. So let\u2019s look at another T-SQL method for killing all connections to a database, except our own.<\/p>\n set @execSql = ''\nselect @execSql = @execSql + 'kill ' +\n\tCONVERT(char(10), spid) + ' '\nfrom master.dbo.sysprocesses\nwhere db_name(dbid) = @databaseName\n AND DBID <> 0\n AND spid <> @@spid\nexec(@execSql)<\/code><\/pre>\n<\/p>\n This method is ten lines of code and is iterating through the current processes executing a KILL<\/a>.aspx) command via dynamic SQL. I don\u2019t like this method, it feels sloppy to me, but most importantly it is difficult to remember and not quickly typed out. Likely, we all have snippet tools to solve that problem but let me show you another way.<\/p>\n This PowerShell method is one that I prefer. Its three lines of code which already speeds up the scripting and I find it easier to remember. Also, I tend to add the Import-Module command to my PowerShell profile<\/a>.aspx), thus removing one more line of code.<\/p>\n Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":" There are some operations that we execute\u00a0in SQL Server which need exclusive access to the database. For everyone who has ever attempted this, I\u2019m sure they\u2019ve seen an\u00a0error like this at least once. There are a couple of ways to handle forcing exclusive access on a database. First we have the ALTER DATABASE method in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-85","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/85","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=85"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"predecessor-version":[{"id":348,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/85\/revisions\/348"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nThis method is highly effective. It sets the database to SINGLE_USER mode and the current connection will take ownership of that single user. In addition, the ROLLBACK IMMEDIATE option will KILL all other connections and force their active transactions to rollback.<\/p>\ndeclare @execSql varchar(1000), @databaseName varchar(100)\n-- Set the database name for which to kill the connections\nset @databaseName = 'MyDB'\n\nImport-Module SQLPS -DisableNameChecking\n$svr = New-Object (\"Microsoft.SqlServer.Management.Smo.Server\") \"localhost\";\n$svr.KillAllProcesses(\"Test1\");<\/code><\/pre>\n
\nWritten by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n