Monday, September 26, 2011

Exclusive access could not be obtained because database is in use-sql server restore database error

While publishing a website to the web server, I took backup of my database. Now I had abc.backup at my hand. Next step was to create a database in the sql server of my host. I did it. Then I uploaded the backup file to the server. When I tried to restore the database I got following error:
Exclusive access could not be obtained because the database is in use
This error normally discourages because we wonder what exclusive access is this that we need to successfully restore the database. After searching a while you come to conclusion that the database to which we are restoring our backup is in use by one or some users you have already added. The popular solution is - temporarily isolate the user from the database Easy, if you run this command at your server:
Use master
go

Alter Database mydbname
SET SINGLE_USER With ROLLBACK IMMEDIATE

 RESTORE DATABASE mydbname
 FROM DISK = 'C:\abc.bak'
Now you are right if you suspect whether we should the roll back to multi user. Yes this way:
Use master;
go

ALTER DATABASE mydbname

SET MULTI_USER;
go
Note that we run the commands against the master database, not the candidate database itself. And did I do the same last time? Nope. I was just happening with a slight mistake. I had created users for the db before I restored the database. So to save yourself from all those stuffs explained above, just create the database in the server, restore your backup file to it and only then create the database users. Done! Thanks.kick it on DotNetKicks.com

2 comments:

SQL Server Recovery said...

This process is taking too much time. I have searched for another approach to resolve this issue & wanted to share with all blog readers for there benefit:

1. First disable the access of another SQL Server login: with this command:
alter login [LOGIN_NAME]
2. After that Set the database in SINGLE_USER mode with this command:
USE [master]
ALTER DATABASE [DATABASE_NAME
3. SET SINGLE_USER;
USE [master]
ALTER DATABASE [DATABASE_NAME
SET SINGLE_USER;
4. Restore the database.
5. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME
SET MULTI_USER;

Anonymous said...

Worked perfect for me thanks

Post a Comment

Hope you liked this post. You can leave your message or you can put your valuable suggestions on this post here. Thanks for the sharing and cooperation!

Popular Posts

Recent Articles