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

Question

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?

Answer 1

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.

Answer 2

I tend to use OFFLINE strictly because on active instances trying to set a database to SINGLE_USER and be the single user that connects is not always consistent. If you set it offline that is it, it is offline.

I will also add that a better practice to do the restore is to have the application stopped or block access from the server. This will save from your error log filling up and prevent connections from occurring once the restore is complete.