Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting exclusive access to restore SQL Server databases


By:   |   Read Comments (12)   |   Related Tips: More > Restore


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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.

sql server error exclusive access

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.

detach database

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 
OR 
ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK AFTER 30 
OR 
ALTER DATABASE [Test4] 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.

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 

RESTORE DATABASE [Test4] 
FROM DISK = 'c:\test4.BAK' 
WITH MOVE 'Test4_Data' TO 'c:\data\Test4.mdf', 
MOVE 'Test4_Log' TO 'c:\data\Test4_log.ldf' 

Once the database has been restored you can put the database back into multi-user access mode using this command:

ALTER DATABASE [Test4] SET MULTI_USER
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 restore tips


Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, February 09, 2017 - 6:36:41 AM - garima Back To Top

No Need to do anything just kill the process SqLWB.exe FROM TAK MANAGER and open sql server and right click on the database and take offline, If it doesn't work then after session is being killed type the command ALTER DATABASE [Test4] SET OFFLINE WITH ROLLBACK IMMEDIATE and then offline. It will work as it worked for me as well.


Monday, September 14, 2015 - 10:35:32 AM - James Ball Back To Top

Excellent article but for those wanting GUI assistance (myself included) I quote this is from MSDN link at bottom. Also how do you get back to multi-user?  

Set DB to Single User GUI method:

To set a database to single-user mode

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Right-click the database to change, and then click Properties.

  3. In the Database Properties dialog box, click the Options page.

  4. From the Restrict Access option, select Single.

  5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

https://msdn.microsoft.com/en-us/library/ms345598.aspx?f=255&MSPPError=-2147217396

For SQL 2016 Prerequisites:
Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. For more information, see 
ALTER DATABASE SET Options (Transact-SQL).


Monday, May 18, 2015 - 5:10:01 AM - Phil Avery Back To Top

What I found was that if you use the SQL Server Enterprise Manager GUI, when you right click the db name, select All Tasks and Detach Database, you are presented with a window that tells you how many connections are using the databse, in my case, 1.  There is an option to clear them, so I clicked 'Clear' and cancelled the window.

Going back into the window the number had changed to zero, so I was then able to perform the restore without a problem.

 


Monday, November 24, 2014 - 5:47:21 AM - Andy Wright Back To Top

Isn't using something like:

 

ALTER DATABASE [test4] SET OFFLINE WITH ROLLBACK IMMEDIATE

 

more effective? Once the restore completes the restored database will be inline and available.

 

If you just set it to single user mode there must be a chance that a new connection gets made just before the restore happens which would then stop the restore from happening.


Monday, June 23, 2014 - 5:47:00 PM - uninfinity Back To Top

Thank you Steban, that worked perfectly!!

 


Tuesday, December 17, 2013 - 5:53:38 PM - Matteus Back To Top

For a quicker solution to get full control on DB just open the SQL Management Studio tool  and select  "properties" using the alternate mouse button for the database in which you want to work and select Options then at the State section modify the Restrict Access and change it to "Single User" then apply,  after that the DB simbol will change and you could work in single mode on the database, so when you get restoring you maybe need to rollback the restrict Access to the previous state.

Regards,

Steban,


Thursday, February 21, 2013 - 3:06:25 AM - shashi Back To Top

 

exe sp_renameDB [dbname2012-2013]','kK'

 

not working?


Thursday, January 19, 2012 - 4:15:17 AM - bojanna mk Back To Top

Thank you sir!!! Really useful when we get struck,in the middle of restore.


Saturday, October 17, 2009 - 8:03:32 PM - admin Back To Top

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]
FROM DISK = 'C:\test.bak'
WITH RESTRICTED_USER

"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.


Wednesday, October 14, 2009 - 6:06:09 PM - Soozie Back To Top

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


Tuesday, October 06, 2009 - 8:18:14 AM - Rajesh Erasani Back To Top

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.


Saturday, September 20, 2008 - 5:24:02 AM - RADIX Back To Top

 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


Learn more about SQL Server tools