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.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This Post Has 7 Comments

  1. abbers

    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.

  2. Michael Snead

    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.

  3. HilaDG

    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!

  4. Johnson

    Nice post i have found another helpful blog.

  5. Laura Scott

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

    1. Mark Perry

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