Sql-server – Quickly restoring a backup over top of an existing database – error “database in use”

restoresql-server-2008-r2

I'm working on a set of scripts (in SQL Server Management Studio) to fix certain data inconsistencies in a customer database (SQL Server 2008 R2).

I got a .bak file with the database, and I restored that to my system, to e.g. SampleDatabase. Now I'm working on my scripts, and after a while, I've fixed most of the issues.

So now I'd like to restore that original .bak file again, to be sure all my scripts do run against the original data, and do what they're supposed to do. I close all the Windows in SQL Server Management Studio, open a fresh new query window in my master database, and issue my RESTORE command:

RESTORE DATABASE [SampleDatabase] 
FROM DISK = N'c:\tmp\sample.bak'
WITH FILE = 1,  
MOVE N'Sample_PRIMARY' TO N'c:\mssql\Sample_PRIMARY.MDF',  
MOVE N'Sample_LOG' TO N'C:\mssql\Sample_LOG.LDF',
NOUNLOAD, REPLACE, STATS = 5

Unfortunately, I now get an error message:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

which seems a bit odd, since I made sure I had closed all the SSMS Windows into that database – why is it still in use?

How can I fix this situation as quickly as possible? The only solution I know that works right now is restarting the MSSQLSERVER service – but that's a rather lengthy and somewhat cumbersome process…

Is there another, more efficient, more streamlined way to get this database "out of use" so I can restore the backup over top of it again to check my scripts?

Best Answer

Variants of the following which uses ROLLBACK IMMEDIATE are common:

:setvar DatabaseName "SampleDatabase"
:setvar BackupDevicePath "D:\Temp\sample.bak"
:setvar DataFilePath "D:\SQLData\I1\"

USE master;
GO

IF (DB_ID('$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO

RESTORE DATABASE 
    [$(DatabaseName)] 
FROM  
    DISK = N'$(BackupDevicePath)'
WITH 
    FILE = 1
    , MOVE N'$(DatabaseName)_Data' TO N'$(DataFilePath)$(DatabaseName).mdf'
    , MOVE N'$(DatabaseName)_Log' TO N'$(DataFilePath)$(DatabaseName).ldf'
    , REPLACE
    , STATS = 1;

Note SQLCMD mode.

Related Question