4

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?

2 Answers 2

4

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.

1
  • 3
    Excellent stuff - thanks so much, Mark! I was confident there had to be an easier way - tapping into the dba.se brain pool proved to be my best choice - once again! :-)
    – marc_s
    Dec 2, 2013 at 17:25
0

Try restricting access to restricted_user in the database options. Do your thing and then set back to multi-user.

1
  • There may be more than 1 restricted user accessing the database, so that may not get you the exclusive control. You will still need to follow up to get SINGLE_USER control.
    – RLF
    Aug 5, 2014 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.