Unable to restore database backup because it is accessed by other connection
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;
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