Automate Database Test Restore in SQL Server

This article talks about automating the database restoration process which is often done manually by the DBA or the infrastructure team responsible for the management of database server(s) and database(s).

This article also highlights the importance of automating database administration tasks such as database backup and restoration to ensure that consistency and reliability of production database(s) are intact.

Additionally, there will be some tips about how to plan and implement database restoration task automation in a real-life scenario.

Database Test Restoration and CheckDB (DBA Perspective)

The importance of SQL Server database backup and restoration from the point of view of the database administrator or the infrastructure team (responsible for database server management) is a lot more than just running backup and restoration since this is one of the ways to ensure that the disaster recovery strategy (DR) is well in place.

This also means that database backup and restore operations are more effective if they’re supported by any other consistency checker mechanism.

Using the DBCC CHECKDB Command

SQL Server comes up with a handy command to check a database’s consistency.

According to the Microsoft documentation, the DBCC CHECKDB command is used to check the logical and physical integrity of all objects in the database by performing a number of different operations.

The successful execution of the command without any errors indicates that the database is in a stable and consistent state.

This command can simply be executed as follows provided that the desired database is connected:

Database Test Restoration Scenario

Let’s consider a real-world scenario in which a DBA is consistently creating backups of the production database.

However, creating backups doesn’t guarantee that the database disaster recovery also known as DR is in place unless the database is restored successfully.

So the database must be restored from the backup in order to ensure that in case of any database incident the data can be restored successfully.

The production database backup should never be restored on the production server for testing purposes.

So how does a DBA ensure that the backup is all well? Simply by restoring the latest backup of the database on another test server.

Once the database backup has been restored successfully, the DBCC CHECKDB command should be executed to make sure that the restored database is consistent and stable.

This is an important but time-consuming task which also requires a lot of care. Ideally, it should be automated – unless your organizational rules and regulations restrict automation of this particular task.

Automating Database Test Restoration

Let’s now see how to automate database test restoration in detail.

Planning Database Test Restore

It is very important to plan your steps so that you can automate them with ease. Planning also requires you to meet the preconditions of SQL task automation.

Preconditions

Please make sure that the following preconditions are met before you automate database restoration tasks in SQL server using the traditional way:

1. Your automation does not conflict with organizational rules and regulations
2. You are authorized to automate this task
3. SQL Agent is running
4. The database server on which the restoration is going to take place should be up and running
5. The database backup process is already automated and backup is sent to a backup drive
6. The backup drive is accessible by the test server

Steps Summary

1. Database backup runs on a server
2. This backup is saved to a backup drive accessible by another test server
3. A SQL Job has been scheduled on the test server to run the restoration of the backup
4. The SQL Job once run can be followed by manually running DBCC CheckDB on the restored database, but can also be automated

Reference Article

Please refer to the article Basics of SQL Server Tasks Automation to automate the database backup task since this article assumes that the database backup task has already been automated and it saves the backup on a backup drive which is accessible by a test server.

Replicating the Database Backup Task

Let us assume that the backup task has been run and a backup has been created on a backup drive.

To replicate this task, we are going to create a sample database called UniversityV7 as follows:

-- (1) Create the UniversityV7 sample database
CREATE DATABASE UniversityV7;
GO

USE UniversityV7

CREATE TABLE [dbo].[Student] (
    [StudentId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (30)  NULL,
    [Course]    VARCHAR (30)  NULL,
    [Marks]     INT           NULL,
    [ExamDate]  DATETIME2 (7) NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC)
);
GO

INSERT INTO [dbo].[Student]
           ([Name]
           ,[Course]
           ,[Marks]
           ,[ExamDate])
     VALUES
           ('Asif'
           ,'Power BI Fundamentals'
           ,85
           ,'01 Jan 2019'),
		   ('Mike'
           ,'Power BI Fundamentals'
           ,80
           ,'01 Jan 2019'),
		   ('Sadaf'
           ,'Power BI Fundamentals'
           ,75
           ,'01 Jan 2019')
		   
GO

Running this script creates and populates the sample database.

Check the database by running the following script:

SELECT [StudentId]
      ,[Name]
      ,[Course]
      ,[Marks]
      ,[ExamDate]
  FROM [UniversityV7].[dbo].[Student]

The output should be as follows:

Let’s assume that the backup task is automated. However, to skip this step, we still have to manually create the database backup and save it to a backup drive accessible by the test server.

Replicate the database backup task by running the following T-SQL script:

-- Backup the UniversityV7 sample database using the date stamp
DECLARE @BackupName VARCHAR(100)
SET @BackupName=CONCAT('C:\Backup\UniversityV7-',FORMAT(GETDATE(),'yyyy-MM-dd'),'.bak')
BACKUP DATABASE UniversityV7 TO DISK=@BackupName WITH COMPRESSION, INIT;
GO

Database Backup Check

Check the database backup by navigating to the folder where we just created the backup and view the backup file:

Connect to another SQL instance

Now, let’s connect to another SQL instance to emulate a test server. The first thing to check is whether the SQL Agent is running or not.

Start SQL Agent (if not running)

Right-click SQL Server Agent and click Start as shown below:

Create a New Job

Next, right-click Jobs, click New Job… and then type the job name as “Database Test Restore”:

Next, select Steps in the left navigation menu, click New, and then type “Database Test Restore” as the step name:

Type the following T-SQL script into the Command input box after making sure that T-SQL type is selected in the corresponding drop-down list.

-- Restore the UniversityV7 sample database 
RESTORE DATABASE UniversityV7 FROM DISK=N'C:\Backup\UniversityV7-2019-08-27.bak'
WITH
MOVE 'UniversityV7' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLTAB\MSSQL\DATA\UniversityV7_Data.mdf',
MOVE 'UniversityV7_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLTAB\MSSQL\DATA\UniversityV7_Log.ldf'
,RECOVERY,REPLACE,STATS=10;
GO

Please note that the path C:\Program Files\Microsoft SQL Server\MSSQL12.SQLNAME\MSSQL\DATA\UniversityV7_Data.mdf provided in this script is for demo purposes only. You have to replace this with the actual path of your test SQL server instance.

Finally, click OK.

Schedule the Job to run in 2 minutes

Right-click the Database Test Restore job under Jobs and click Properties:

Click New, type “Every 2 minutes (testing)”, and set the schedule to run after two minutes:

After a few minutes, connect to the test server and expand the Databases node to see that the UniversityV7 database has been restored successfully by an automated SQL Server Task (Job).

Please feel free to run DBCC CheckDB to ensure that the database restoration process is all fine.

Congratulations! You have successfully automated the database test restoration process.

Things to do

Now that you can automate database backup and test restoration, please try completing the following tasks to improve your skills:

1. Add DBCC CheckDB as a part of the automated database restoration process
2. Automate the backup on one server and restoring it on another test server
3. Automate the database backup and test restoration tasks for multiple databases