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.
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.
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.
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!
Nice post i have found another helpful blog.
what can I do in case of .BAK file corruption?
If the .BAK is corrupt, then you need to find another backup unfortunately.
Useful post. This helps in fixing the restore error