I want to close the existing connections to an MS SQL Server so that I can do a restore on that database programatically.
This question is related to
sql
sql-server
You can use Cursor like that:
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
I wrote about that in my blog here: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor
In more recent versions of SQL Server Management studio, you can now right click on a database and 'Take Database Offline'. This gives you the option to Drop All Active Connections to the database.
Perfect solution provided by 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
You get "close existing connections to destination database" option only in "Databases context >> Restore Wizard" and NOT ON context of any particular database.
Right Click on the Databases under your Server-Name as shown below:
and select the option: "Restore Database..." from it.
In the "Restore Database" wizard,
Here you can find the checkbox saying, "close existing connections to destination database"
Just check it, and you can proceed for the restore operation.
It automatically will resume all connections after completion of the Restore.
in restore wizard click "close existing connections to destination database"
in Detach Database wizard click "Drop connection" item.
Found it here: http://awesomesql.wordpress.com/2010/02/08/script-to-drop-all-connections-to-a-database/
DECLARE @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
Source: Stackoverflow.com