 |
|
Getting exclusive access to restore SQL Server databases
|
By: Greg Robidoux | Read Comments (5) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More |
|
Problem
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. This may be a scheduled process or it may be an ad hoc process. 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. What options are there to ensure you have exclusive database access, so the restore process will work without issue?
Solution
There are a couple of options that you have to ensure that you have exclusive access to the database. The following outlines a few of these options and how this task can be simplified.
Kill Users
For this option to work you need to ensure that once you kill the connection, that the connection doesn't reconnect. In some systems where you have automated connections occurring, this may not be the best option. Take a look at this prior tip, Die Sucker - Killing SQL Server Process Ids (spids).
Using GUI to do restore
In Enterprise Manager and SQL Server Management Studio there is not an option to kill all of the users when restoring a database. You can go through the restore steps, but if you click on restore the process will start, but it will not complete. Eventually it will time out and you will get an error message like this: So this is not an option if you have open transactions and connections to the database.
Detach Database, Reattach and Restore
Another possible approach would be to detach the database. When doing this through the GUI you have the option of dropping existing connections first. 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.
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. So this is not really a good option either, plus there are too many steps.
ALTER DATABASE
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. The command looks something like one of the following.
ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE |
- WITH ROLLBACK IMMEDIATE - this option doesn't wait for transactions to complete it just begins rolling back all open transactions
- WITH ROLLBACK AFTER nnn - this option will rollback all open transactions after waiting nnn seconds for the open transactions to complete. In our example we are specifying that the process should wait 30 seconds before rolling back any open transactions.
- WITH NO_WAIT - this option will only set the database to single user mode if all transactions have been completed. It waits for a specified period of time and if the transactions are not complete the process will fail. This is the cleanest approach, because it doesn't rollback any transactions, but it will not always work if there are open transactions.
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.
Note: when using the ROLLBACK option you are rolling back any open transactions that still exist for the database. 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. For test and development systems since you are doing a restore you don'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.
Summary
Once the database is in single user mode it is now easy to perform the restore process. Here is a sample set of code that puts the database in single user mode and does the restore.
ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE |
Next Steps
- If you need to refresh test and development environments on a set schedule, put this technique in place on your systems. This can be setup as a scheduled job and run unattended.
- Take a look at these other backup and recovery tips
Last Update: 1/8/2008
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Saturday, September 20, 2008 - 5:24:02 AM - RADIX | Read The Tip |
|
Your tip is really amazing. I like it MOST. It solved my prbloem and you will be wonder I have searched lots of sites for the same problem but not found any answer. Thank You Very Much
Radix |
|
| Tuesday, October 06, 2009 - 8:18:14 AM - Rajesh Erasani | Read The Tip |
|
How can the database be altered to a single user mode even before the database exists in the server? The restore process itself creates the database and then we can alter the database to single user mode. Can you help me with how we can restore a database in single user mode? The problem I am facing is that I am trying to restore a database and another process from another connection is locking the database before the restore is finished and hence the restore process is waiting on the other process to finish which is causing it to never finish restoring the database. Please help. Thanks. |
|
| Wednesday, October 14, 2009 - 6:06:09 PM - Soozie | Read The Tip |
|
I had the same issue as you. You have to find the service that is using it now. Usually it is SQLAgent. Stop the service for each of the services until you find the right one. Then change back to multi user and restart the services. Good Luck |
|
| Saturday, October 17, 2009 - 8:03:32 PM - admin | Read The Tip |
|
You can use the restricted user option when restoring, so that after the restore only certain accounts can access the database. This is the command. RESTORE DATABASE [test] "Restricts access for the newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In SQL Server 2005, RESTRICTED_USER replaces the DBO_ONLY option. DBO_ONLY is available only for backward compatibility." If you need to restore a database file and transaction logs use the NORECOVERY option for all of your restores and then you can issue this command to recover the database and put it in the restricted state. RESTORE DATABASE [test] WITH RESTRICTED_USER, RECOVERY This can also be done using SSMS if you check the "Restrict access to the restored database" If other processes have sysadmin rights or db_owner rights to this database then they will have access to the database. If that is the case you can then use the code in the tip to change to single user mode. |
|
| Thursday, January 19, 2012 - 4:15:17 AM - bojanna mk | Read The Tip |
|
Thank you sir!!! Really useful when we get struck,in the middle of restore. |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |