MsSQL – Nicovs' Blog

If you need to restore a Microsoft SQL Database, you might run into the problem that there are still users connection to the database.

It’s not easy to drop those connections… lots of info that I find on google use

ALTERDATABASE DB_NAME SET SINGLE_USER WITHROLLBACK IMMEDIATE
--do stuff
ALTERDATABASEDB_NAME SET MULTI_USER

ALTERDATABASE DB_NAME SET SINGLE_USER WITHROLLBACK IMMEDIATE

--do stuff

ALTERDATABASEDB_NAME SET MULTI_USER

And this doesn’t always seem to work on SQL2008 or SQL2012.

But… here’s a handy script that I found on Stackoverflow:

Replace the DB_Name variable with the name of your DB for which you want to drop the connection:

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

USE master

SET NOCOUNT ON

DECLARE @DBName varchar(50)

DECLARE @spidstr varchar(8000)

DECLARE @ConnKilled smallint

SET @ConnKilled=0

SET @spidstr = ''

Set @DBName = 'DB_NAME'

IF db_id(@DBName) < 4

BEGIN

PRINT 'Connections to system databases cannot be killed'

RETURN

END

SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '

FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0

BEGIN

EXEC(@spidstr)

SELECT @ConnKilled = COUNT(1)

FROM master..sysprocesses WHERE dbid=db_id(@DBName)

END

I am using Tara’s excellent MsSQL Backup script to backup my SQL databases. This is an excellent way to backup SQL DB’s if you have an SQL Express version installed. Heck, I even use this when higher SQL versions are installed (with maintenance plan possibilities and stuff).
On top of that, I needed a script which was able to:
– Move my MsSQL BAK files from a local disk to a network path. This is because the backup script does not allow to backup to UNC paths.
– Clean up older backups on the destination directory.

I found (actually, I already knew) that Robocopy was a very nifty tool to do things like this.

My Howto:

Prepare your destination (backup) drive on your network by creating 2 ‘dummy dir’s:

1 actual backup destination folder on your remote fileserver (eg \\bck_svr\SQL_Backup)
1 directory to move your older files too ( eg \\bck_svr\delete)
1 always directory that acts as a ‘delete mirror’ ( eg \\bck_svr\EMPTY)

After that, just download and install the Resource Kit Tools, and create a BAT script that looks like:

c:
cd "C:\Program Files (x86)\Windows Resource Kits\Tools"

REM Move files older than 4 days to a Delete dir, but create them as empty files which fastens everything up (and also directly clears the diskspace :))
robocopy.exe \\bck_svr\\SQLBackup \\bck_svr\Delete /e /CREATE /MOVE /MINAGE:4 

REM Delete the contents of the 'Delete' dir. 
robocopy.exe \\bck_svr\EMPTY \\bck_svr\Delete /MIR /E 

REM Make a new copy of the Dirictoy to backup. 
robocopy "d:\sql_backup" \\bck_svr\SQL_Backup /mov /s

cd "C:\Program Files (x86)\Windows Resource Kits\Tools"

REM Move files older than 4 days to a Delete dir, but create them as empty files which fastens everything up (and also directly clears the diskspace :))

robocopy.exe \\bck_svr\\SQLBackup \\bck_svr\Delete /e /CREATE /MOVE /MINAGE:4

REM Delete the contents of the 'Delete' dir.

robocopy.exe \\bck_svr\EMPTY \\bck_svr\Delete /MIR /E

REM Make a new copy of the Dirictoy to backup.

robocopy "d:\sql_backup" \\bck_svr\SQL_Backup /mov /s

Category: MsSQL

MsSQL Database: Drop connections

Using Robocopy to copy backups and maintain file retention.