SqlServer2005 恢复数据库时出现Exclusive access could not be

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!