I kept running into issues when I tried to restore a database from a backup file. The error I kept getting was:
Restore failed for Server ‘localhostSQLExpress’.
System.Data.SqlClient.SqlError: Exclusive access could not be
obtained because the database is in use.
For local sql instances, it’s not a huge deal since I can just restart the db server and clear the connections. But in a managed/shared environment, that’s not possible. I googled quite a bit for a solution, but didn’t really find anything that would work. So I asked the newsgroups for some help and they came up with putting the db in single user mode before restoring the db. That was cool and all but ended up locking me out of the db at my hoster. So what I had to do was run the single user mode command and the restore command as one query. Something like:
ALTER DATABASE [your_db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [your_db_name] FROM DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupyour_db_name.bak’ WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
The restore line can automatically be scripted for you by Sql Management Studio. Just click the Script icon at the top of the restore dialog. Maybe MS will add an option to clear connections on restore in a future version of the management studio… One other note is that the restore command will automatically set the restored database in multiuser mode.
Hope this helps someone…
If you need to set the database back to multi user mode, you can use the following command:
ALTER DATABASE [your_db_name] SET MULTI_USER WITH ROLLBACK IMMEDIATE