Restoring Database from one environment to another environment in SQL Server

As a DBA, we are required to restored database from one environment to another environment all the time.

The following steps are involved in restoring database.

Step 1. Find the mdf and ldf files to restore
Step 2. Do Actual Restore of the database
Step 3 Find orphan object for user login and fix them. ( This is important as different environment have different users --Production version test or dev environment)

Step 1.

Find the files to restore .



USE MASTER
GO

DECLARE @Path VARCHAR(128)
       ,      @Backup VARCHAR(128)
       ,      @Filename VARCHAR(128)
       ,      @dbname VARCHAR(128)

-- Set Variables
SET @Path     = 'C\BackupFiles' + '\' ---Path of the file..Replace with your path
SET @Backup = 'Name of the database backup files' + '.bak' -- File Name --- Replace with your
SET @filename = @path + @Backup
SET @dbname = 'Development1'  ---Name of Database ---replace with your


-- Restore FileList
RESTORE FILELISTONLY
FROM DISK = @Filename

Step 2

In real environment, you may have replication going on the server where you want to restore. So we have to make sure that we kill replication.


:connect SQLTST
:setvar dbname "Development1"
:setvar delay "00:00:00.003"
:setvar max_retries "5"
:r "C\Scripts\UtilityScripts\Recovery\KillDB.sql"

USE MASTER
GO

DECLARE @Path VARCHAR(128)
       ,    @Backup VARCHAR(128)
       ,    @Filename VARCHAR(128)
       ,    @dbname VARCHAR(128)

   -- Set Variables
SET @Path     = 'C\Backup' + '\' ---Path of the file..Replace with your path
SET @Backup = 'Name of the database backup files' + '.bak' -- File Name --- Replace with your
SET @filename = @path + @Backup
SET @dbname = 'Development1' --Which Database to restore

RESTORE DATABASE @dbname
FROM   DISK = @Filename
WITH   MOVE 'Development1' TO 'C:\SQL\Development1_data.mdf' ---Replace with Phyiscal Name value which was given in first step.  If there are multiple mdf file
,      MOVE 'Development1_log       TO 'C:\SQL\Development1_log.ldf'
,      STATS = 5
,       replace -- May be needed if replacing existing database.


Step 3:

Fix orphan object

EXECUTE sp_change_users_login 'Report'


This will give a list of username where the objects are orphan. For example if a user 'Home' exist


Then run this

EXECUTE sys.sp_change_users_login 'Auto_Fix', 'Home'