Showing posts with label restore files. Show all posts
Showing posts with label restore files. Show all posts

Thursday, December 13, 2012

Simple T-SQL Script to Kill All Connections to a MSSQL Database


When restoring an MSSQL database, it is common to receive an error similar to this one: 

"The database could not be exclusively locked to perform the operation."
Msg 5030, Level 16, State 12, Line 1 

This error can occur in MSSQL 2005, 2008 or 2012.
There are a number of workarounds for this. Some will tell you to disable the website's application pool in IIS, but this doesnt help if someone remotely is connected using SQL Management Studio. You can go into Performance Monitor and manually kill each process, but often the processes jump up faster than you can disable them by hand. 
Where that is the case, perform the following procedure. Assuming your restore job task window is properly configured and just open, keep it open. You can right click on the database server and select New Query without closing the restore job task window, which wastes time when you must recreate it. Then, enter the following script into the query menu (be sure to update the variable @DB_NAME with the actual name of your database). This script will sever all connections immediately. You can then left click on your restore job task window and click OK to retry the restore. Assuming the restore task is correctly configured, it will complete without further issue. 

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

(hat tip to Adam Lacey)

How To Fix MSSQL Error 3219

You've done everything right with your MSSQL database restore. All of your versions match. All connections to the database have been killed. Your BAK file is valid, you have adequate permissions to both it and the proposed MDF and LDF file paths. And yet, when you attempt to restore using MSSQL 2005 or 2008, the task fails with the following:

The file or filegroup X cannot be selected for this operation. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219)

Often the most infuriating problems are the one's with the easiest solution. To fix this, simply perform "Restore Database" instead of "Restore Files".

Billing systems development now available

Good news for current and future clients of Josh Wieder Technical Consulting : customers can now retain a variety of unique services related...