5
--first command

use master

Go
-- second command
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

Go 
-- Third command
RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\AcctDB.bak' 
WITH  FILE = 1,  
MOVE N' AcctDB ' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct.mdf',  
MOVE N' AcctDB _log' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct_log.ldf',  
NOUNLOAD,  
REPLACE,  
STATS = 10

GO

--forth command

ALTER DATABASE MyDB SET MULTI_USER 

Go

I run the above commands, one by one to restore database in different server. But sometime I cannot restore the database after changed to single user mode, because it is accessed by other connection. If I run the whole script except the last part together would that block other connection so I will be the only user?

6

I would just go with your script with a little modification. Add a waitfor delay. Also, make sure that Instant file initialization is enabled to cut down the restore time.

--first command
use master
go

-- second command
alter database MyDB    
set OFFLINE with rollback IMMEDIATE -- single_user might give you issues if sql agent connects and grabs the only connection. Offline is more preferred.
waitfor delay '00:00:05' -- 5 sec delay .. no other spid takes our space !

-- Third command
restore database [MyDB]
from disk = N'D:\Restore_Backup\Restore_05042015\AcctDB.bak'
with file = 1
    ,move N' AcctDB ' to N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct.mdf'
    ,move N' AcctDB _log' to N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct_log.ldf'
    ,NOUNLOAD
    ,REPLACE
    ,STATS = 10


-- Fourth command
alter database MyDB   
set MULTI_USER with rollback IMMEDIATE
go
  • The db is in single user mode and we grab the only connection and this prevents from other connections. I always use waitfor delay to ensure my restore is always successful. – Kin Shah May 20 '15 at 12:37
6

Running this all at once does not guarantee that no one will open a connection between you putting it in single-user mode and you starting the restore. Instead, you can put the database in offline mode rather of single-user mode to avoid this problem.

E.g.

ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
  • To be clear, if your active window (which is running the ALTER followed by the RESTORE) starts with a USE [MyDB] instead of USE MASTER, then it will be the "single user" that maintains the connection. BUT there are some situations that won't work, like if you're calling 3rd-party backup software to perform the restore (since that will open a fresh connection). In that case, SET OFFLINE works great. – BradC Apr 14 '17 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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