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 · 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

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

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  quot Messages quot  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 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

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

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

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

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

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

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

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

Examples related to sql-server

Examples related to sql-server-2005

Examples related to backup

Examples related to restore

Examples related to disconnect