{"id":55,"date":"2014-10-01T16:48:25","date_gmt":"2014-10-01T16:48:25","guid":{"rendered":"http:\/\/www.codereview.co\/?p=55"},"modified":"2014-10-08T11:42:03","modified_gmt":"2014-10-08T11:42:03","slug":"exclusive-access-for-restoring-the-sql-server-databases","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/exclusive-access-for-restoring-the-sql-server-databases\/","title":{"rendered":"How to get  exclusive access for restoring the SQL Server databases?"},"content":{"rendered":"<p><span style=\"color: #000000;\"><strong>Scenario proposal<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">A task that you may be faced with as a DBA is the need to refresh a test or development database on a periodic basis.\u00a0 This may be a scheduled process or it may be an ad hoc process.\u00a0 One of the things that you need to do when restoring a database is to ensure you have exclusive access to the database otherwise the restore process will not work.\u00a0 What options are there to ensure you have exclusive database access, so the restore process will work without issue?<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Solution<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">There are a couple of options that you have to ensure that you have exclusive access to the database.\u00a0 The following outlines a few of these options and how this task can be simplified.<\/span><\/p>\n<p><span style=\"color: #000000;\"><em>Kill Users<\/em><\/span><\/p>\n<p><span style=\"color: #000000;\">For this option to work you need to ensure that once you kill the connection, that the connection doesn&#8217;t reconnect.\u00a0 In some systems where you have automated connections occurring, this may not be the best option.<\/span><\/p>\n<p><span style=\"color: #000000;\"><em>Using GUI to do restore<\/em><\/span><\/p>\n<p><span style=\"color: #000000;\">In Enterprise Manager and SQL Server Management Studio there is not an option to kill all of the users when restoring a database.\u00a0 You can go through the restore steps, but if you click on restore the process will start, but it will not complete.\u00a0 Eventually it will time out and you will get an error message like this:\u00a0 So this is not an option if you have open transactions and connections to the database.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><img decoding=\"async\" loading=\"lazy\" class=\"alignnone  wp-image-65\" src=\"http:\/\/i1.wp.com\/www.codereview.co\/wp-content\/uploads\/2014\/10\/1.jpg?resize=368%2C98\" alt=\"1\" srcset=\"http:\/\/i1.wp.com\/www.codereview.co\/wp-content\/uploads\/2014\/10\/1.jpg?resize=368%2C98 300w, http:\/\/www.codereview.co\/wp-content\/uploads\/2014\/10\/1.jpg 586w\" sizes=\"(max-width: 368px) 100vw, 368px\" data-recalc-dims=\"1\" \/><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><em>Detach Database, Reattach and Restore<\/em><\/span><\/p>\n<p><span style=\"color: #000000;\">Another possible approach would be to detach the database.\u00a0 When doing this through the GUI you have the option of dropping existing connections first.\u00a0 The way this is done is that the existing connections are killed first, the database is put into single user mode and then the database is taken offline.<\/span><\/p>\n<p><span style=\"color: #000000;\">With this method you could detach with drop connections, reattach the database, put the database is single user mode and then do the restore, but there is still a chance that a connection could be made and you would be back in the same spot.\u00a0 So this is not really a good option either, plus there are too many steps.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><img decoding=\"async\" loading=\"lazy\" class=\"alignnone  wp-image-66\" src=\"http:\/\/i2.wp.com\/www.codereview.co\/wp-content\/uploads\/2014\/10\/2.jpg?resize=376%2C128\" alt=\"2\" srcset=\"http:\/\/i2.wp.com\/www.codereview.co\/wp-content\/uploads\/2014\/10\/2.jpg?resize=376%2C128 300w, http:\/\/www.codereview.co\/wp-content\/uploads\/2014\/10\/2.jpg 403w\" sizes=\"(max-width: 376px) 100vw, 376px\" data-recalc-dims=\"1\" \/><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><em>ALTER DATABASE<\/em> <\/span><\/p>\n<p><span style=\"color: #000000;\">The best approach for doing this would be to use the ALTER DATABASE command to set the database to single user mode along with rolling back any open transactions.\u00a0 The command looks something like one of the following.<\/span><\/p>\n<table width=\"100%\">\n<tbody>\n<tr>\n<td>ALTER\u00a0DATABASE\u00a0[Test4]\u00a0SET\u00a0SINGLE_USER\u00a0WITH\u00a0ROLLBACK\u00a0IMMEDIATE OR ALTER\u00a0DATABASE\u00a0[Test4]\u00a0SET\u00a0SINGLE_USER\u00a0WITH\u00a0ROLLBACK\u00a0AFTER\u00a030 OR ALTER\u00a0DATABASE\u00a0[Test4]\u00a0SET\u00a0SINGLE_USER\u00a0WITH\u00a0NO_WAIT<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<ul>\n<li><span style=\"color: #000000;\">WITH ROLLBACK IMMEDIATE &#8211; this option doesn&#8217;t wait for transactions to complete it just begins rolling back all open transactions<\/span><\/li>\n<li><span style=\"color: #000000;\">WITH ROLLBACK AFTER nnn &#8211; this option will rollback all open transactions after waiting nnn seconds for the open transactions to complete.\u00a0 In our example we are specifying that the process should wait 30 seconds before rolling back any open transactions.<\/span><\/li>\n<li><span style=\"color: #000000;\">WITH NO_WAIT &#8211; this option will only set the database to single user mode if all transactions have been completed.\u00a0 It waits for a specified period of time and if the transactions are not complete the process will fail.\u00a0 This is the cleanest approach, because it doesn&#8217;t rollback any transactions, but it will not always work if there are open transactions.<\/span><\/li>\n<\/ul>\n<p><span style=\"color: #000000;\">Once the database has been put in single user mode, you have exclusive access to the database and can then do the restore without a problem.<\/span><\/p>\n<p><span style=\"color: #000000;\">Note: when using the ROLLBACK option you are rolling back any open transactions that still exist for the database.\u00a0 The rollback process should work without issue, but if you have very long running transactions the rollback process could take a long time, so be aware of what is running on your systems.\u00a0 For test and development systems since you are doing a restore you don&#8217;t care about the transactions anyway, so rolling things back should not be an issue, but you still need to be aware that long running transactions may take some time to rollback.<\/span><\/p>\n<p><span style=\"color: #000000;\"><em>Summary<\/em> <\/span><\/p>\n<p><span style=\"color: #000000;\">Once the database is in single user mode it is now easy to perform the restore process.\u00a0 Here is a sample set of code that puts the database in single user mode and does the restore.<\/span><\/p>\n<table width=\"100%\">\n<tbody>\n<tr>\n<td>ALTER\u00a0DATABASE\u00a0[Test4]\u00a0SET\u00a0SINGLE_USER\u00a0WITH\u00a0ROLLBACK\u00a0IMMEDIATE\u00a0 RESTORE\u00a0DATABASE\u00a0[Test4] FROM\u00a0DISK\u00a0=\u00a0&#8216;c:\\test4.BAK&#8217; WITH\u00a0MOVE\u00a0&#8216;Test4_Data&#8217;\u00a0TO\u00a0&#8216;c:\\data\\Test4.mdf&#8217;, MOVE\u00a0&#8216;Test4_Log&#8217;\u00a0TO\u00a0&#8216;c:\\data\\Test4_log.ldf&#8217;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>\u00a0<\/strong><\/p>\n<p><span style=\"color: #000000;\"><strong>Thanks for reading this article,<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Next steps :<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1. Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><b><strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2. Add this script to your database toolkit<\/strong><\/b><\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3. Comment below if you need any assistance<\/strong><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Scenario proposal A task that you may be faced with as a DBA is the need to refresh a test or development database on a periodic basis.\u00a0 This may be a scheduled process or it may be an ad hoc process.\u00a0 One of the things that you need to do when restoring a database is [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,3],"tags":[7,11,10,8,9,13,12],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/55"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=55"}],"version-history":[{"count":12,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/55\/revisions"}],"predecessor-version":[{"id":113,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/55\/revisions\/113"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=55"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=55"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=55"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}