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

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

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

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

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

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

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

[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