SqlServer2005 恢复数据库时出现Exclusive access could not be obtained because the database is in use 的解决方法

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 ‘localhost/SQLExpress’.

Additional Information:
  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 Files/Microsoft SQL Server/MSSQL.1/MSSQL/Backup/your_db_name.bak’ WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

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… 

 

转自：http://biasecurities.com/blog/2007/how-to-restore-a-db-when-the-db-is-in-use/

 

由于恢复数据库时需要对数据库进行独占的访问，在恢复之前你必须中止其他用户与数据库的连接。

解决方法：
在恢复数据库前：
方法一.打开Management Studio.
    a). 右键点击你的数据库，Task ->Take Offline.
    b). 右键点击你的数据库，Task ->Bring Online.

方法二.执行如下的Query:

    Use Master

    Alter Database [YOURDB]

        SET SINGLE_USER With ROLLBACK IMMEDIATE

    在恢复数据库后如果需要恢复会普通多用户模式：

    Use master;
    Go

    Alter Database [YOURDB]

        SET MULTI_USER

    Go

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ftkghost/archive/2008/09/05/2885682.aspx