Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Getting exclusive access to a SQL Server database for restore



By:

Overview

When restoring a database, one of the things you need to do is ensure that you have exclusive access to the database.  If any other users are in the database the restore will fail.

Explanation

When trying to do a restore, if any other user is in the database you will see these types of error messages:

T-SQL

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

SSMS


Getting Exclusive Access

To get exclusive access, all other connections need to be dropped or the database that they are in needs to be changed so they are not using the database you are trying to restore.  You can use sp_who2 or SSMS to see what connections are using the database you are trying to restore.

Using KILL
One option to get exclusive access is to use the KILL command to kill each connection that is using the database., but be aware of what connections you are killing and the rollback issues that may need to occur.  See this tip for more information on how to do this.

Using ALTER DATABASE
Another option is to put the database in single user mode and then do the restore.  This also does a rollback depending on the option you use, but will do all connections at once.  See this tip for more information on how to do this.

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
GO

Last Update: 3/17/2009




More SQL Server Solutions











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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, October 04, 2017 - 11:50:51 PM - Bill Altmann Back To Top

May need to start with

USE MASTER

GO

 


Thursday, September 07, 2017 - 4:59:13 PM - John C Back To Top

Thank you for the tips.

 


Thursday, June 29, 2017 - 1:12:17 AM - David Austin Back To Top

 

 

Thanks, that was helpful  :)


Monday, September 05, 2016 - 6:40:56 AM - Israel Friedman Back To Top

 To avoid this error in SSMS, check "Close existing connections to destination database" in the Restore Database Options page.


Friday, July 08, 2016 - 11:10:04 AM - Greg Robidoux Back To Top

Hi Charlie,

You would need to put the database back into multi-user mode again.

ALTER DATABASE AdventureWorks SET MULTI_USER

 


Friday, July 08, 2016 - 10:52:55 AM - Charlie Back To Top

The ALTER DATABASE command worked perfectly. Does anything need to be flipped back once the restore is complete? ie SET SINGLE_USER OFF or something similar?


Monday, May 23, 2016 - 10:56:13 PM - Ann Back To Top

 

 Thank you so much, its very helpful


Learn more about SQL Server tools