When restoring a backup, how do I disconnect all active connections?

Question

My SQL Server 2005 doesn't restore a backup because of active connections. How can I force it?

Epilepsy

Do you always want to kill all connections to the database that you want to "restore over"? Or will there be times when you do not want to kill existing connections? Also, do you have to worry about connection pooling? – Thermic

Answer 1

SQL Server Management Studio 2005

When you right click on a database and click Tasks and then click Detach Database, it brings up a dialog with the active connections.

By clicking on the hyperlink under "Messages" you can kill the active connections.

You can then kill those connections without detaching the database.

More information here.

SQL Server Management Studio 2008

The interface has changed for SQL Server Management studio 2008, here are the steps (via: Tim Leung)

1. Right-click the server in Object Explorer and select 'Activity Monitor'.
2. When this opens, expand the Processes group.
3. Now use the drop-down to filter the results by database name.
4. Kill off the server connections by selecting the right-click 'Kill Process' option.

Bandage

If you're having the same issue as @Ryan, it's probably because you're using Management Studio 2008 (or above), rather than Management Studio 2005. To do the same thing in Management Studio 2008, right-click your server in Object Explorer and select 'Activity Monitor'. When this opens, expand the Processes group. Now use the drop-down to filter the results by database name. You can now kill off your connections by selecting the right-click 'Kill Process' option. – Rochester

Answer 2

You want to set your db to single user mode, do the restore, then set it back to multiuser:

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Official reference: https://msdn.microsoft.com/en-us/library/ms345598.aspx

Leninism

Rather than issuing an IMMEDIATE ROLLBACK , it may be pertinent to only ROLLBACK after a specificed DELAY, thereby giving user queries an opporunity to complete naturally. – Soluble

Good point, updated to rollback to include the AFTER 60 command to allow current queries to complete – Leninism

Hi @brendan, what if rollback takes more than 60 secs? thanks – Agueda

If you are restoring a database, the open transactions will be lost whether you ROLLBACK IMMEDIATE or ROLLBACK AFTER 60. The only way to save that data is to perform another backup after the rollback. But you're restoring from a different backup. So, what's the point of waiting? Am I missing something? – Dilettantism

@DMason, I'm curious about this question as well. Does using the single_user with rollback mode prevent new connections during the wait time? If so, I wonder if it's a cleaner/nicer way to at least let read-only actions complete rather then end them abruptly? – Secede

For the newbie to single user mode (like me), it helps to do this while the database you are trying to backup is not the one you have set active. Set the active database to Master prior to issuing these commands and you will be golden... – Mutz

Answer 3

This code worked for me, it kills all existing connections of a database. All you have to do is change the line Set @dbname = 'databaseName' so it has your database name.

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

after this I was able to restore it

Clink

This was the fastest approach (SingleUserMode * 20 = 60s, Kill * 20 = 5s). – Ingmar

It did not work for me. Database is still in use. I use SQL Server 2008. – Incorporating

I've found running that code several times, one right after the other, will EVENTUALLY do the trick. Sometimes something sneaks in between your KILL and restore. And sometimes you have to run the kill THEN the restore one right after the other. – Mcgaw

Depends entirely on the aggressiveness of the application trying to reconnect. Couple of lazy users? Works great. High-volume app server that reconnects in under a second? Not so much. – Dehydrate

Answer 4

Try this:

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

Bijugate

Answer 5

Restarting SQL server will disconnect users. Easiest way I've found - good also if you want to take the server offline.

But for some very wierd reason the 'Take Offline' option doesn't do this reliably and can hang or confuse the management console. Restarting then taking offline works

Sometimes this is an option - if for instance you've stopped a webserver that is the source of the connections.

Barroom

+1. The accepted answer won't work for SQL Express (e.g. in a dev environment) because SQL Express doesn't have Activity Monitor – Masticate

@MattFrear: This is not true! At least in 2008 R2 Express I see a toolbar button and a context menu entry on the server node. – Rammer

Restarting an entire SQL server will kill connections to all databases. A server may be supporting many databases yet only one needs to be restored now. – Gelsemium

This is absolutely the worst way to kill connections to 1 database. Especially if you have many other databases still being used by other users. I highly advise AGAINST using this method. It's 100%, total overkill!! – Mcgaw

@JohnWaclawski I don't know about worst but certainly laziest - that's why I said sometimes. It doesn't really save any time over other methods anyway – Barroom

Answer 6

I ran across this problem while automating a restore proccess in SQL Server 2008. My (successfull) approach was a mix of two of the answers provided.

First, I run across all the connections of said database, and kill them.

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

Then, I set the database to a single_user mode

ALTER DATABASE dbName SET SINGLE_USER

Then, I run the restore...

RESTORE DATABASE and whatnot

Kill the connections again

(same query as above)

And set the database back to multi_user.

ALTER DATABASE dbName SET MULTI_USER

This way, I ensure that there are no connections holding up the database before setting to single mode, since the former will freeze if there are.

Laudatory

Answer 7

None of these were working for me, couldn't delete or disconnect current users. Also couldn't see any active connections to the DB. Restarting SQL Server (Right click and select Restart) allowed me to do it.

Allelomorph

Answer 8

To add to advice already given, if you have a web app running through IIS that uses the DB, you may also need to stop (not recycle) the app pool for the app while you restore, then re-start. Stopping the app pool kills off active http connections and doesn't allow any more, which could otherwise end up allowing processes to be triggered that connect to and thereby lock the database. This is a known issue for example with the Umbraco Content Management System when restoring its database

Yentai

Answer 9

None of the above worked for me. My database didn't show any active connections using Activity Monitor or sp_who. I ultimately had to:

• Right click the database node
• Select "Detach..."
• Check the "Drop Connections" box
• Reattach

Not the most elegant solution but it works and it doesn't require restarting SQL Server (not an option for me, since the DB server hosted a bunch of other databases)

Russi

This is total overkill. Use the KILL code above. Works on hundreds of restore jobs for me. – Mcgaw

The database I was working with wouldn't KILL everything--however, it may have been an issue with their setup. I agree that's much easier generally. – Russi

Answer 10

I prefer to do like this,

alter database set offline with rollback immediate

and then restore your database. after that,

alter database set online with rollback immediate

Asexual