Sql-server – Which method is better to drop all user connections before a restore Change to single user mode or Take offline the database

restoresql server

When we restore a database from prod to dev which method is better to disconnect other connection accessing the database? I am on SQL Server 2008r2

1. Change to Single user mode

use master

Go

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

Go 



RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\DB.bak' 
WITH  FILE = 1,  
MOVE N'DB' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB.mdf',  
MOVE DB_log' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf',  
NOUNLOAD,  
REPLACE,  
STATS = 10

GO


ALTER DATABASE MyDB SET MULTI_USER 

Go

2. Take offline the database and restore database 

USE master

GO

ALTER DATABASE MyDB
SET OFFLINE WITH ROLLBACK IMMEDIATE

GO


RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\DB.bak' 
WITH  FILE = 1,  
MOVE N'DB' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB.mdf',  
MOVE DB_log' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf',  
NOUNLOAD,  
REPLACE,  
STATS = 10
GO



ALTER DATABASE MyDB
SET OFFLINE 

GO

Which method would assure I will be the only user so I can do the restoration without a problem?

Best Answer

I would set it offline. I say this because SINGLE_USER mode is generally used to allow an administrator to perform some kind on maintenance on a database without users attempting to access and possibly interfering with the maintenance.

It doesn't really matter either way for a restore as your users will experience the same thing which is they wont be able to connect. One possible annoyance with the SINGLE_USER approach would be if a user stole the single session before you and blocked you from performing your restore.

Related Question