{"id":296,"date":"2011-01-18T08:40:25","date_gmt":"2011-01-17T22:40:25","guid":{"rendered":"http:\/\/jcrawfor74.wordpress.com\/?p=296"},"modified":"2024-06-08T20:45:21","modified_gmt":"2024-06-08T10:45:21","slug":"restore-sql-server-database-disconnect-all-users","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2011\/01\/18\/restore-sql-server-database-disconnect-all-users\/","title":{"rendered":"Restore SQL Server Database &#8211; disconnect all users"},"content":{"rendered":"<div id=\"ntsbl-942721906\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>\n<p>Hi,<\/p>\n\n\n\n<p>Often when you try to restore a database there will be connections to the DB, which will stop the restore from succeeding.<\/p>\n\n\n\n<p>What I do is use the wizard to configure the restore, and then click the script button, to script the restore to the clipboard, so that I can run it as a TSQL statement.<\/p>\n\n\n\n<p>The trick is you want to disconnect all sessions, so you send the Database into single user mode which will disconnect all sessions. After the restore you then set the database back to normal.<\/p>\n\n\n\n<p>The code would look like<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">Alter Database YOURDB Set SINGLE_USER with Rollback Immediate\nGO\n\nRESTORE DATABASE [YOURDB] FROM DISK = N'F:\\SQLBackup\\YOURDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10\nGO\n\nAlter Database YOURDB Set MULTI_USER\nGO<\/code><\/pre>\n\n\n\n<p>The important bits are the Alter Database statements either side of the restore database script.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, Often when you try to restore a database there will be connections to the DB, which will stop the restore from succeeding. What I [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13],"tags":[],"class_list":["post-296","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/296","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=296"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/296\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}