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

User · Accepted Answer

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)

Right-click the 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.
Kill off the server connections by selecting the right-click 'Kill Process' option.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   spid End   after this I was able to restore it

[sql-server] When restoring a backup, how do I disconnect all active connections?

SQL Server Management Studio 2005

SQL Server Management Studio 2008

Examples related to sql-server

Examples related to sql-server-2005

Examples related to backup

Examples related to restore

Examples related to disconnect