2

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?

  • I came cross some article suggested that set offline the database is better to drop all the connection than changing to single user mode. I always use changing single user mode. but I never set offline database so I am not sure which one is better or preferable. that why asked to learn the preferable one. – SQL_DBA_Bini May 21 '15 at 21:41
4

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.

1

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.

  • how to stop application or block access. Last time when I want that happen I had to ask the application owner to stop it. Is there a way I can stop It with out asking them? – SQL_DBA_Bini May 22 '15 at 13:26
  • No they would need to get involved, but if you are restoring the applications database why wouldn't they be? Regarding blocking access to the instance you would end up blocking all access with most methods so if it is shared instance it would not be an option. However, most methods are to block the port from incoming traffic, or simply disable the protocols for the instance. This is why I tend to just opt to have the application folks shut their stuff off. – Shawn Melton May 22 '15 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.