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

Question

由于活动连接，My SQL Server 2005 无法恢复备份。 我怎样才能强迫它？

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

Answer 2

SQL Server Management Studio 2005

当您右键单击数据库并单击任务，然后单击分离数据库时，它会弹出一个包含活动连接的对话框。

通过单击“消息”下的超链接，您可以终止活动连接。

然后，您可以终止这些连接，而无需分离数据库。

更多信息请参见此处。

SQL Server Management Studio 2008

SQL Server Management Studio 2008 的界面已更改，步骤如下（通过：Tim Leung)

1. 在对象资源管理器中右键单击服务器并选择“活动监视器”。
2. 打开后，展开进程组。
3. 现在使用下拉列表按数据库名称过滤结果。
4. 通过选择右键单击“终止进程”选项来终止服务器连接。

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

之后我能够恢复它

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

Answer 5

重新启动 SQL Server 将断开用户连接。 我发现的最简单的方法 - 如果你想让服务器离线也很好。

但由于一些非常奇怪的原因，“脱机”选项不能可靠地执行此操作，并且可能会挂起或混淆管理控制台。 重新启动然后脱机工作

有时这是一个选项 - 例如，如果您停止了作为连接源的网络服务器。

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)

并将数据库设置回多用户模式。

ALTER DATABASE dbName SET MULTI_USER

这样，我确保在设置为单一模式之前没有任何连接占用数据库，因为如果有连接，前者就会冻结。

Answer 7

这些都不适合我，无法删除或断开当前用户的连接。 也看不到任何与数据库的活动连接。 重新启动 SQL Server（右键单击并选择重新启动）允许我执行此操作。

Answer 8

要添加到已经给出的建议中，如果您有一个通过 IIS 运行且使用数据库的 Web 应用程序，您可能还需要在恢复时停止（而不是回收）该应用程序的应用程序池，然后重新启动-开始。 停止应用程序池会终止活动的 http 连接，并且不再允许更多连接，否则最终可能会允许触发连接数据库的进程，从而锁定数据库。 这是 Umbraco 内容管理系统在恢复数据库时的一个已知问题

Answer 9

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

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

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

Answer 10

我更喜欢这样做，

更改数据库设置为脱机并立即回滚

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

更改数据库设置并立即回滚