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.
If you try to do a restore of a database you may encounter this error: “Exclusive access could not be obtained because the database is in use.”
What options are there to ensure you have exclusive database access, so the restore process will work without issue?
Solution
If you have tried to a restore a database that is in use by other connections, you may have seen this error message before.
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 new connections are not established. In some systems where you have automated connections occurring, this may not be the best option. Take a look at this prior tip, Kill SQL Server Process Ids (spids), to learn how you can kill all sessions connected to a specific database.
Using the SSMS GUI to do restore
In SQL Server Management Studio there is a setting on the Options page to “Close existing connections to destination database”.
Detach Database, Reattach and Restore
Another possible approach would be to detach the database. When doing this through the SSMS 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 to Single User Mode and Restore
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 [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- OR this option
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK AFTER 30
--OR this option
ALTER DATABASE [Test] SET SINGLE_USER WITH NO_WAIT
- 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.
Alter Database to Single User Mode and Restore
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 [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [Test]
FROM DISK = 'c:\test.BAK'
WITH MOVE 'Test_Data' TO 'c:\data\Test.mdf', MOVE 'Test_Log' TO 'c:\data\Test_log.ldf', REPLACE
Alter Database to Multi User Mode
Once the database has been restored you may need to put in back in multi-user mode using this command:
ALTER DATABASE [Test] SET MULTI_USER
Alter Database to Offline and Restore
Another option is to take the database offline and then restore the database as one of our readers suggested. This is similar to the above approach, but can be done as follows:
ALTER DATABASE [Test] SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [Test]
FROM DISK = 'c:\test.BAK'
WITH MOVE 'Test_Data' TO 'c:\data\Test.mdf', MOVE 'Test_Log' TO 'c:\data\Test_log.ldf', REPLACE
Once the restore is done, the database is put back online and can be used.
Next Steps
- Be aware that if you do this with a production database you will kill and rollback all active sessions, so be careful where you use this.
- 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 restore tips
