Unable to restore database backup because it is accessed by other connection

Question

--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?

Answer 1

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

Answer 2

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;