在恢复备份时，我该如何断开所有活动连接？

Question

我的 SQL Server 2005 因为存在活动连接无法还原备份，我该如何强制还原？

- Jader Dias

---

你是否总是想要杀死所有连接到你想要“恢复”的数据库？或者有时你不想杀死现有的连接吗？此外，你需要担心连接池吗？ - Philip Kelley

Answer 1

你想将你的数据库设置为单用户模式，进行恢复操作后再将其设置回多用户模式：

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK AFTER 60 --this will give your current connections 60 seconds to complete

--Do Actual Restore
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUp\YourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\Data\YourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\Data\YourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.  If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

参考文献：Pinal Dave (http://blog.SQLAuthority.com)

官方参考资料：https://msdn.microsoft.com/en-us/library/ms345598.aspx

- brendan

---

与其立即回滚，也许更合适的是在指定延迟后才回滚，从而给用户查询自然完成的机会。 - John Sansom

好的，已更新回滚以包括 AFTER 60 命令，以允许当前查询完成。 - brendan

嗨@brendan，如果回滚需要超过60秒怎么办？谢谢。 - user3583912

如果你正在恢复一个数据库，无论你使用ROLLBACK IMMEDIATE还是ROLLBACK AFTER 60，未提交的事务都将丢失。唯一保存这些数据的方法是在回滚后执行另一个备份。但是你正在从不同的备份进行恢复。那么等待有什么意义呢？我是否漏掉了什么重要的信息？ - Dave Mason

@DMason，我也对这个问题很好奇。在等待时间内使用带有回滚模式的single_user是否会阻止新连接？如果是这样，我想知道它是否是一种更干净/更好的方式，至少让只读操作完成而不是突然结束它们？ - Jason

对于像我这样的新手来说，进入单用户模式时最好在你尝试备份的数据库不是当前活动数据库的情况下进行。在执行这些命令之前将活动数据库设置为Master，这样你就可以顺利完成了... - DaveN59

Answer 2

SQL Server Management Studio 2005

当您右键单击数据库并单击任务，然后单击分离数据库时，它会显示一个带有活动连接的对话框。

通过点击“消息”下的超链接，您可以终止活动连接。 然后，您可以在不分离数据库的情况下终止这些连接。 更多信息在这里。 SQL Server Management Studio 2008 界面已更改为SQL Server Management Studio 2008，以下是步骤（通过：Tim Leung） 1.在对象资源管理器中右键单击服务器，然后选择“活动监视器”。 2.打开时，展开“进程”组。 3.现在使用下拉列表按数据库名称过滤结果。 4.通过选择右键单击“终止进程”选项来终止服务器连接。

- George Stocker

---

如果您遇到了与@Ryan相同的问题，那很可能是因为您正在使用管理工具2008（或更高版本），而不是管理工具2005。要在管理工具2008中执行相同的操作，请在“对象资源管理器”中右键单击服务器，然后选择“活动监视器”。打开后，展开“进程”组。现在使用下拉菜单按数据库名称过滤结果。您可以通过选择右键单击“终止进程”选项来终止连接。 - Tim Leung

Answer 3

这段代码对我很有用，它可以关闭数据库的所有现有连接。 你只需要更改 Set @dbname = 'databaseName' 这一行，将其替换为你自己的数据库名称。

Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

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

之后我能够将其恢复

- RagnaRock

---

这是最快的方法（SingleUserMode * 20 = 60秒，Kill * 20 = 5秒）。 - Karson

它对我没有用。数据库仍在使用中。我使用的是SQL Server 2008。 - Marek Bar

我发现连续运行那段代码几次，最终会奏效。有时候，在你的KILL和恢复之间会悄悄插入一些东西。有时候你必须要连续运行KILL和恢复。 - John Waclawski

完全取决于尝试重新连接的应用程序的攻击性。有几个懒惰的用户？效果很好。重新连接时间不到一秒钟的高容量应用服务器？就不那么理想了。 - BradC

Answer 4

试试这个：

DECLARE UserCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
    spid
FROM
    master.dbo.sysprocesses
WHERE DB_NAME(dbid) = 'dbname'--replace the dbname with your database
DECLARE @spid SMALLINT
DECLARE @SQLCommand VARCHAR(300)
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO
    @spid
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLCommand = 'KILL ' + CAST(@spid AS VARCHAR)
    EXECUTE(@SQLCommand)
    FETCH NEXT FROM UserCursor INTO
        @spid
END
CLOSE UserCursor
DEALLOCATE UserCursor
GO

- user2276214

Answer 5

重新启动SQL服务器将会断开用户连接。我发现最简单的方法是把服务器脱机，这也适用于你想要让服务器下线的情况。

但由于某些非常奇怪的原因，“脱机”选项并不能可靠地实现这一点，可能会挂起或混淆管理控制台。先重启再脱机是行得通的。

有时候这是一个选项 - 例如，如果你已经停止了连接源头的Web服务器。

- Simon_Weaver

---

+1. 接受的答案在 SQL Express（例如在开发环境中）不起作用，因为 SQL Express 没有活动监视器。 - Matt Frear

@MattFrear：这不是真的！至少在2008 R2 Express中，我看到了服务器节点上的工具栏按钮和上下文菜单项。 - Stephan

重启整个 SQL 服务器将中断对所有数据库的连接。一个服务器可能支持多个数据库，但现在只需要恢复其中一个。 - Ross Presser

这绝对是断开与1个数据库连接的最糟糕的方法，尤其是如果你还有许多其他数据库正在被其他用户使用。我强烈反对使用这种方法，它是百分之百的过度杀伤！ - John Waclawski

@JohnWaclawski 我不知道最差的是什么，但肯定是最懒的 - 这就是为什么我说有时候。无论如何，这种方法并不能真正节省时间。 - Simon_Weaver

Answer 6

我在自动恢复SQL Server 2008过程中遇到了这个问题。 我的（成功的）解决方法是两个答案的混合。 首先，我查找所有该数据库的连接并终止它们。

DECLARE @SPID int = (SELECT TOP 1 SPID FROM sys.sysprocess WHERE dbid = db_id('dbName'))
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = top 1 spid from master.dbo.sysprocesses
        where dbid = db_id('dbName')
End

然后，我将数据库设置为单用户模式。

ALTER DATABASE dbName SET SINGLE_USER

然后，我运行了还原操作...

RESTORE DATABASE and whatnot

再次断开连接

(same query as above)

并将数据库设置为 multi_user 模式。

ALTER DATABASE dbName SET MULTI_USER

在将数据库设置为单用户模式之前，我会确保没有连接正在占用它，因为如果有连接存在，前者会被冻结。

- Eric Wu

Answer 7

除了之前提供的建议，如果您有一个通过IIS运行并使用数据库的Web应用程序，您可能还需要在恢复时停止（而不是重启）该应用程序的应用程序池，然后重新启动。停止应用程序池会终止活动的HTTP连接并阻止更多的连接，否则可能会触发连接到并锁定数据库的进程，这是例如Umbraco内容管理系统在恢复其数据库时已知的问题。

- Chris Halcrow

Answer 8

对我来说，这些都没有起作用，无法删除或断开当前用户。也无法看到与数据库的任何活动连接。重新启动SQL Server（右键单击并选择“重新启动”）使我能够做到。

- The Coder

Answer 9

以上方法都对我没用。使用活动监视器或sp_who时，我的数据库没有显示任何活动连接。最终我不得不：

• 右键单击数据库节点
• 选择“分离…”
• 勾选“删除连接”框
• 重新附加

这不是最优雅的解决方案，但它有效，并且不需要重启SQL Server（对我来说不是一个选项，因为DB服务器托管了许多其他数据库）。

- Brent Waggoner

---

这太过了。使用上面的KILL代码即可。对于我来说，它适用于数百个还原作业。 - John Waclawski

我正在使用的数据库并没有完全崩溃 - 但可能是他们设置的问题。我同意这通常更容易。 - Brent Waggoner

Answer 10

我更喜欢这样做：

将数据库设置为脱机状态，并立即回滚

然后恢复你的数据库。 之后，

将数据库设置为联机状态，并立即回滚

- Aniyan Kolathur