When restoring a backup, how do I disconnect all active connections?
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
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.