When restoring a backup, how do I disconnect all a

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Detach Screen http://www.kodyaz.com/images/articles/kill-all-processes/detach-database.JPG

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.

Answer 4

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)

Answer 5

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

Answer 6

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.