如何获得独占访问SQL Server 2005数据库的恢复？

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

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

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.

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.

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

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.

好吧，我搞砸了数据并需要恢复。不幸的是，我在办公室里有另一个同事，他正在研究另一个项目，并使用相同的数据库服务器进行开发。为了好，我想恢复而不重新启动SQL Server并可能破坏他的工作。

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

有没有办法在T-SQL中编写脚本以便能够进行独占访问或删除所有连接？

7 个解决方案

#1

You can force the database offline and drop connections with:

您可以强制数据库脱机并删除连接：

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

Or you can

或者你可以

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.

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

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

对不起我没想到/正确读书。你可以回到网上和备份。在T-SQL片段上还有一篇关于Stack Overflow的帖子，用于删除所有连接而不是首先脱机：SQL Server的隐藏功能

#2

I find this vastly faster and generally better than taking offline. Do read about it in MSDN so you understand the caveats. If using aysnc statistics, you have to turn those off, as well.

我发现这比离线要快得多，通常要好一些。在MSDN中阅读它，以便您了解警告。如果使用aysnc统计信息，您也必须关闭它们。

-- set single user, terminate connections
ALTER DATABASE [target] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE ...
ALTER DATABASE [target] SET MULTI_USER

The "with rollback immediate" is the essential "termination" clause. Leaving it out waits forever. A nicer version of the above gives user transactions a few seconds to terminate.

“立即回滚”是必不可少的“终止”条款。离开它等待永远。上述更好的版本为用户事务提供了几秒钟的终止时间。

ALTER DATABASE [target] SET SINGLE_USER WITH ROLLBACK AFTER 5

Offline is a good idea if you want to copy database files around, a scenario that can be handy in desktop editions of SQL. Too heavy for this scenario. If offline, this would be preferred. SQL is moving away from sp_dboption.

如果要复制数据库文件，离线是一个好主意，这种情况在桌面版SQL中很方便。这种情况太重了。如果离线，这将是首选。 SQL正在远离sp_dboption。

ALTER DATABASE [target] SET OFFLINE WITH ROLLBACK AFTER 5

#3

@mattlant - that's what I was looking for. I bring it over here so it's in the thread.

@mattlant - 这就是我想要的。我把它带到这里，所以它在线程中。

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

#4

So far this worked for me. I right clicked on the database > Tasks > Detach...

到目前为止，这对我有用。我右键单击数据库>任务>分离...

This brought up a screen that allows you to view all active connections. You can then go through and disconnect each connection. When you hit ok you've detached the database and need to Attach the database. Right-click on Databases and choose attach, pick you mdf file and the db is attached. At this point you should have exclusive access to restore.

这会打开一个允许您查看所有活动连接的屏幕。然后，您可以通过并断开每个连接。当你点击确定你已经分离数据库并需要附加数据库。右键单击Databases并选择attach，选择mdf文件并附加db。此时，您应该拥有恢复的独占权限。

Note: I tested this by connecting to one of his databases from my local machine and from the server dropped the connections to my database and I didn't lose my connection to his database.

注意：我通过从本地计算机连接到他的一个数据库来测试这个，并且从服务器上删除了与我的数据库的连接，并且我没有丢失与他的数据库的连接。

#5

I'd suggest talking to your co-worker, and asking him to leave the database. (And make him aware of the problem, because he might lose changes he's made when you restore.)

我建议与你的同事交谈，并要求他离开数据库。（并让他意识到这个问题，因为他可能会失去你在恢复时所做的改变。）

That's far better than dropping his connections, or setting exclusive access which might cause him some inconvenience.

这比放弃他的连接或设置独家访问要好得多，这可能会给他带来一些不便。

#6

Well, you can kill SQL processes and sessions with KILL.

好吧，你可以用KILL杀死SQL进程和会话。

But if you just drop all his current connections, won't he just reopen them?

但是，如果你只是放弃他目前的所有关系，他不会只是重新打开它们吗？

You probably just have to go tell him you're going to restore from a backup so he stops connecting for a bit.

你可能只需要告诉他你将从备份中恢复，所以他会停止连接。

#7

First, you cannot restore a database unless you are the only person currently connected and you have administrator rights. You must first tell your co-worker that you need to restore and ask him or her to be sure to script out any changes that might not be on the backup media. This is only polite and keeps co-workers from killing you.

首先，除非您是当前连接的唯一人并且您具有管理员权限，否则无法还原数据库。您必须首先告诉您的同事您需要还原并要求他或她确保编写可能不在备份媒体上的任何更改。这只是礼貌，让同事不会杀了你。

Next you set the database to single-user mode. You can look up how to do this in Books Online. This prevents anyone else from connecting while you are doing this and gives you a chance to kill existing connections. It is important to go to single-user mode, because no one else should be doing anything to the database while you restore.

接下来，将数据库设置为单用户模式。您可以在联机丛书中查找如何执行此操作。这样可以防止其他人在您执行此操作时进行连接，并使您有机会终止现有连接。进入单用户模式非常重要，因为在还原时没有其他人应该对数据库执行任何操作。

Then you run the restore process.

然后运行还原过程。

#1