Showing posts with label because it is currently in use.. Show all posts
Showing posts with label because it is currently in use.. Show all posts

Monday, 14 May 2012

Dropping active connections before restoring a database

When restoring test environment databases sometimes you just want to ignore all the current connections to the database and just restore it. 
You will normally see the message:
Msg 3702, Level 16, State 3, Line 1
Cannot drop database “AdventureWorks” because it is currently in use.
Some people use the approach of running and sp_who2 to see the active connections to that database and then run the KILL <SPID> command to drop the connections. This can be a bit tedious especially if there are a lot of connections and you need to kill each spid individually

To get around this you can drop the existing connections to your database using the command 
ALTER DATABASE [<YourDatabaseNameHere>] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

T-SQL - AdventureWorks Example

The following is an example of dropping the AdventureWorks connections and then restoring the database

-- this command will drop the existing connections to the database
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- normal restore command
RESTORE DATABASE [AdventureWorks] 
FROM  DISK = N'D:\Backups\AdventureWorks.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO