All we need is an easy explanation of the problem, so here it is.
--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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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.
ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂