Getting exclusive access to restore SQL Server database

By:   |   Comments (13)   |   Related: > Restore


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.

sql server error exclusive access

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

sql server error exclusive access

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.

detach database

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, September 9, 2020 - 9:59:17 AM - Greg Robidoux Back To Top (86442)
Thanks Richard. I will look into this and update the article. When this was first written there wasn't a way to do that, so I will make an update to the article.

Thanks
Greg

Tuesday, September 8, 2020 - 6:01:00 PM - Richard Back To Top (86434)
"In SQL Server Management Studio there is not an option to kill all of the users when restoring a database"

Ummm, yes there is. It is on the Options tab of Restore Database "Close existing connections to destination database"

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

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 (37205)

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 (35379)

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 (32359)

Thank you Steban, that worked perfectly!!

 

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

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 (22329)

 

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

 

not working?

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

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 (4233)

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 (4193)

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 6, 2009 - 8:18:14 AM - Rajesh Erasani Back To Top (4141)

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 (1853)

 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















get free sql tips
agree to terms