我想关闭与 MS SQL Server 的现有连接,以便可以通过程序实现对该数据库的还原。
我想关闭与 MS SQL Server 的现有连接,以便可以通过程序实现对该数据库的还原。
这将断开其他所有用户的连接,只留下你自己:
alter database YourDb set single_user with rollback immediate
注意:不要忘记
alter database YourDb set MULTI_USER
完成后!
ALTER DATABASE YourDb SET MULTI_USER
! - Tyler ForsytheDECLARE @dbname NVARCHAR(128)
SET @dbname = 'DB name here'
-- db to drop connections
DECLARE @processid INT
SELECT @processid = MIN(spid)
FROM master.dbo.sysprocesses
WHERE dbid = DB_ID(@dbname)
WHILE @processid IS NOT NULL
BEGIN
EXEC ('KILL ' + @processid)
SELECT @processid = MIN(spid)
FROM master.dbo.sysprocesses
WHERE dbid = DB_ID(@dbname)
END
您可以这样使用光标:
USE master
GO
DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)
SET @Database = 'AdventureWorks2016CTP3'
DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database
OPEN Murderer
FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
EXEC (@SQL)
PRINT ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
FETCH NEXT FROM Murderer INTO @SPID
END
CLOSE Murderer
DEALLOCATE Murderer
我在这里的博客中写了关于此的内容:http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor
Stev.org 提供的完美解决方案: http://www.stev.org/post/2011/03/01/MS-SQL-Kill-connections-by-host.aspx
(此链接为英文原文,需要使用翻译工具进行翻译)IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KillConnectionsHost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KillConnectionsHost]
GO
/****** Object: StoredProcedure [dbo].[KillConnectionsHost] Script Date: 10/26/2012 13:59:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[KillConnectionsHost] @hostname varchar(MAX)
AS
DECLARE @spid int
DECLARE @sql varchar(MAX)
DECLARE cur CURSOR FOR
SELECT spid FROM sys.sysprocesses P
JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
JOIN sys.sysusers U ON (P.uid = U.uid)
WHERE hostname = @hostname AND hostname != ''
AND P.spid != @@SPID
OPEN cur
FETCH NEXT FROM cur
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(varchar, @spid)
SET @sql = 'KILL ' + RTRIM(@spid)
PRINT @sql
EXEC(@sql)
FETCH NEXT FROM cur
INTO @spid
END
CLOSE cur
DEALLOCATE cur
GO
您只能在“数据库上下文>>还原向导”中找到“关闭目标数据库的现有连接”选项,而不能在任何特定数据库的上下文中找到该选项。
右键单击您服务器名称下方的数据库,如下所示:
并从中选择“还原数据库...”选项。
在“还原数据库”向导中,
在这里,您可以找到一个复选框,上面写着“关闭目标数据库的现有连接”
只需勾选它,就可以进行还原操作。
完成还原后,它会自动恢复所有连接。