如何获得对 SQL Server 2005 数据库的独占访问权限来恢复?

问题描述

每当我在 SQL Server 中恢复我的数据库备份时，都会出现以下错误:

Whenever I restore a backup of my database in SQL Server I am presented with the following error:

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

通常为了解决这个问题，我只是重新启动服务器.当我们在我们的开发机器上的本地实例上进行开发时，这很好.但是我们有一些程序员需要访问数据库，以及让每个人编写他们的更改脚本并将其放入 Subversion 正在变成一场噩梦.不管怎样，我们的简单解决方案是将它放在办公室的共享服务器上，并偶尔备份服务器，以防有人搞砸了数据.

Usually to get around this I just restart the server. This was fine when we were developing on our local instance on our development machines. But we have a few programmers that need to access the database, and the logistics of having everyone script their changes and drop them into Subversion was becoming a nightmare. Regardless our simple solution was to put it on a shared server in the office and backup the server occasionally in case someone screwed up the data.

好吧，我搞砸了数据，需要恢复.不幸的是，我在办公室有另一个同事正在从事另一个项目，并且正在使用相同的数据库服务器进行开发.为善起见，我想在不重新启动 SQL Server 和可能中断他的工作的情况下进行恢复.

Well, I screwed up the data and needed to restore. Unfortunately, I have another co-worker in the office who is working on another project and is using the same database server for development. To be nice I'd like to restore without restarting the SQL Server and possibly disrupting his work.

有没有办法在 T-SQL 中编写脚本以获取独占访问或删除所有连接?

Is there a way to script in T-SQL to be able to take exclusive access or to drop all connections?

推荐答案

您可以通过以下方式强制数据库脱机并断开连接:

You can force the database offline and drop connections with:

EXEC sp_dboption N'yourDatabase', N'offline', N'true'

或者你可以

ALTER DATABASE [yourDatabase] SET OFFLINE WITH
ROLLBACK AFTER 60 SECONDS

回滚指定是否正在执行任何操作.在那段时间之后，它们将被回滚.所以它提供了一些保护.

Rollback specifies if anything is executing. After that period they will be rolled back. So it provides some protection.

抱歉，我没有正确思考/阅读.您可以重新联机并备份.在 Stack Overflow 上还有一篇关于 T-SQL 片段的帖子，用于删除所有连接而不是先离线连接:SQL Server 的隐藏功能

Sorry I wasn't thinking/reading right. You could bing back online and backup. There was also a post on Stack Overflow on a T-SQL snippet for dropping all connections rather than binging offline first: Hidden Features of SQL Server

这篇关于如何获得对 SQL Server 2005 数据库的独占访问权限来恢复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！