Exclusive database access for a SQL Server restore



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.


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


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.



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.

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.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 

Comments For This Article

Thursday, February 9, 2023 - 9:20:03 AM - Greg Robidoux Back To Top (90903)
Hi Vijay,

You could either drop the database like you did or restore over the existing secondary database using the REPLACE option.


Thursday, February 9, 2023 - 12:33:10 AM - Vijay Telure Back To Top (90898)
Hi Greg, while setting up fresh log shipping configuration. I delete database from secondary server then it works.is there any other way plz suggest.

Wednesday, February 8, 2023 - 9:24:40 AM - Greg Robidoux Back To Top (90895)
Hi Vijay, are you just setting up log shipping or is log shipping already in place and you are getting this error?


Wednesday, February 8, 2023 - 2:55:19 AM - Vijay Telure Back To Top (90893)
I am also facing this issue on configuring Log shipping to secondary database

Friday, November 19, 2021 - 9:28:06 AM - Greg Robidoux Back To Top (89470)
Hi David, can you provide any additional details? That doesn't make sense if the database doesn't already exist. Also, just check the names of the database files (data and log) to make sure the file names that are trying to be restored do not exist.


Thursday, November 18, 2021 - 6:44:26 PM - David Moss Back To Top (89464)
I'm getting that error when restoring a backup to a new database. The database doesn't exist yet so how can someone be accessing it?

Monday, September 2, 2019 - 8:51:04 AM - Gopinath Back To Top (82210)


Saturday, November 24, 2018 - 7:41:53 AM - pcnerds53 Back To Top (78316)

It helped me a lot, It took me off from a problem I was having in a running intervention. So, thanks a lot for the tip. Keep doing that job, we all "Pros" thanks a lot. :)

Wednesday, October 4, 2017 - 11:50:51 PM - Bill Altmann Back To Top (66920)

May need to start with




Thursday, September 7, 2017 - 4:59:13 PM - John C Back To Top (66027)

Thank you for the tips.


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



Thanks, that was helpful  :)

Monday, September 5, 2016 - 6:40:56 AM - Israel Friedman Back To Top (43264)

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

Friday, July 8, 2016 - 11:10:04 AM - Greg Robidoux Back To Top (41841)

Hi Charlie,

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



Friday, July 8, 2016 - 10:52:55 AM - Charlie Back To Top (41840)

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


 Thank you so much, its very helpful

get free sql tips
agree to terms