6

Exclusive Access Could Not Be Obtained Error During SQL Restore

Ever try to restore a .bak file to Microsoft SQL Server and get the following error? “Exclusive access could not be obtained because the database is in use”

One way get the restore working again is to use this script;

alter database db_name
set offline with rollback immediate
alter database db_name
set online

This clears the connections and then you can try your restore.

But sometimes that does not even work.

Another solutions is to Right Click on Properties -> Options menu. At the bottom, Restrict Access option is set to MULTI_USER. Change it to SINGLE_USER. Then restore the database. After the restore, the Restrict Access will be changed back to MULTI_USER. Double check to be sure.

 

Click Here to Leave a Comment Below 6 comments
abbers - December 3, 2015

Thanks for the tip on closing existing database connections WITHOUT having to write a single line of SQL code, simply by changing the database to SINGLE_USER using SQL Management server Studio. Why type commands when I can just point and click? That’s so 1980s.

Reply
Michael Snead - February 11, 2016

I thought the first solution was fantastic. I wrote a batch file which, nightly, restores my database to my tablet PC for using while I am away from my more powerful desktop system which updates it. It’s been a p.i.t.a. to constantly have to delete the database if it has been used.

Nice touch. Worked for me.

Reply
HilaDG - April 8, 2016

Just wanted to say thank you for the Second Tip. I’ve worked all day to sort out this error and you definitely helped me out here!

Reply
Johnson - April 12, 2016

Nice post i have found another helpful blog.

Reply
Laura Scott - January 24, 2017

what can I do in case of .BAK file corruption?

Reply
    Mark Perry - February 10, 2017

    If the .BAK is corrupt, then you need to find another backup unfortunately.

    Reply

Leave a Reply: