Script for restoring Sitecore databases on DEV, QA and UAT environment

There are some task which are very important but takes time during website development and maintenance. One such task is restoring databases.

After every SPRINT, I have to take backup of Production Sitecore master and web databases and need to restore on Staging, QA and DEV environment. The size of each database is 12 GB ;)

This tasks are very time consuming where one need to wait for SQL server response and complete the restoration process of such a huge database.

I thought that instead of doing this restoration from SQL Server management studio, let’s try with SQL query.

I found a great script on http://blog.sqlauthority.com/ .

There are 4 steps involved with this restoration query

1. Find the Logical name of data and log file from database backup file

RESTORE FILELISTONLY FROM DISK = 'D:\Backup\TestSitecore_Core_20160112014154.BAK'

GO

2. If you are going to restore on existing database, then run below query for making database to single user mode. If you are going to restore as a new database then skip this query 

-- Restore to an existing database

-- Make Database to single user Mode

ALTER DATABASE TestCoreDB1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 3. This is the main query which restore below CORE database.

-- Restore Database

RESTORE DATABASE TestCoreDB1

FROM DISK = N'D:\Backup\TestSitecore_Core_20160112014154.BAK'

WITH  REPLACE, RECOVERY, 

MOVE N'Sitecore.Core.Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\TestCoreDB1_01Aug.mdf', 

MOVE N'Sitecore.Core.Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\TestCoreDB1_01Aug.ldf'

GO

Where Sitecore.Core.Data and Sitecore.Core.Log is the logical name returned by first query.

4. Skip this query if you have restored file for a new database or if no error occurred in step 3.

Run this query only if your database goes into single user mode or any error occurred. This query will set the database on multi user mode.

/*If there is no error in statement before database will be in multiuser

mode. If error occurs please execute following command it will convert database in multi user.*/

ALTER DATABASE TestCoreDB1 SET MULTI_USER

GO

That’s it. This script make my life easy and in all DEV, QA and UAT environment, every time I just need to change the database name and backup file path. Rest of the things are taking care by script.

Knowledge of SQL server and its trick can make Sitecore guy and of course gals life easy.

Please leave your comments or share this trick if it’s useful for you.