Implementing Automated Database Backup and Restore with Default Means

Implementing Automated Database Backup and Restore with Default Means
3.5 (70%) 6 vote[s]

Introduction

You can find a lot of guides on how to backup and restore databases. In this one, we’ll show how this can be done using the default MS SQL Server means.

This example will cover a number of approaches – from checking the database’s integrity before backing it up to restoring the database from a previously created backup copy.

The solution

Firstly, let’s look at the overall algorithm we’ll use for backing up a database:

1) Defining which databases need to be backed up
2) Checking the integrity of the chosen databases
3) Creating a backup (full, differential or transaction log copy) for each of the chosen databases
4) Checking the created backup copies
5) Compressing the transaction logs (if needed)

Below, you can find an implementation example of this algorithm.

To define which databases need to be backed up, we’ll create the following table:

The database identifier is located in the first column, ‘FullPathBackup’ contains the path for full backup copy creation (for example, ‘disk:\…\’), and the DiffPathBackup and LogPathBackup contain full paths for creation of differential and transaction log copies respectively. If the DiffPathBackup or LogPathBackup columns are empty, then the differential and/or transaction log copy for this database will not be created.

We can also create a representation based on this table:

This representation allows you to effectively check which databases are participating in the backup process.

Now, let’s create a representation which displays database file information from the sys.master_files system representation:

To create full backup copies, let’s implement the following stored procedure:

Code

According to the code, we can see that this procedure provides a solution for the remaining steps of the backup copy creation algorithm.

Procedures which create differential and transaction log copies are implemented in a similar way:

Code

As checking databases for integrity takes a lot of resources, we can omit it while creating a differential backup copy.

Code

As said above, checking databases for integrity is a resource-heavy task. Combined with the fact that transaction log backup copies usually need to be created quite often, this gives us a reason to omit integrity checking while creating a transaction log copy.

Please also keep in mind that full backup copies of ‘master’, ‘msdb’ and ‘model’ databases need to be done periodically.

To automate the backup copy creation process, you’ll just need to place a call of the previously implemented procedures into the Windows Task Scheduler, agent jobs or any similar available service.

You will need to set the call frequency for each of those procedures individually based on the load peaks, activity plateaus etc.

The basic approach is as follows:

1) Creating a full backup copy once a day
2) Creating differential backup copies every 2-4 hours
3) Creating transaction log backup copies every 5-60 minutes

Please keep in mind that usually databases participate in the fail-safe and quick access system. And, if the later uses transaction log backup copies, it is vitally important not to interfere with the procedure. More specifically, this means that transaction log copies should not be created by several different processes – if this happens, the backup sequence from these copies will be lost.

Here, we have seen examples of each database being processed sequentially, one at a time. However, we can achieve parallel processing in production environment – allowing for several backup copies to be made simultaneously. This can be approached in a few different ways. For example, by calling the following stored procedure:

Here, asynchrony is achieved by dynamically creating the Agent jobs, executing and deleting them afterwards.

Now, let’s look at the general algorithm for restoring databases from backup copies previously created in a different/test environment:

1) Defining which databases should be restored and the location of their backup copies
2) Restoring the databases
3) Checking the restored databases for integrity

Now, we’ll look at an implementation of an algorithm which restores a database from a full backup copy. For a differential copy, the procedure is similar – the only difference being that a full backup copy needs to be restored firsthand, followed by the differential copy.

To define which databases should be restored, as well as the location of their backup copies, let’s create two tables as shown below:

Here, the purpose of the columns is analogous to those from the [srv].[BackupSettings] table. The only difference being that the full path will be used to locate the backup copies for restoration, and not to create new ones.

This table is needed to define the complete file names of the database being restored, which are then used for further transfer (for example, [SourcePathRestore]=’Logical file name’ and [TargetPathRestore]= ‘disk:\…\Physical file name’, while [Ext]= ‘File extension’)

Actually, we can define logical names of the database files using the following query:

Getting information about backup copies located in a file can be done in this way:

Next, we have an implementation of a stored procedure used for restoring a database from a full backup copy and checking it for data integrity:

Code

To specify which full backup copy should be used for restoration, a specially structured filename is used:

<databse name>_Full_backup_<year>_<month_number>_<day_number>.bak

To automate this database restoration process, the call of the stored procedure we implemented should be placed into the Windows Task Scheduler, Agent jobs or any similar available service.

You can see the most recent database backup copies using the following representation:

The result

In this guide, we have looked at an implementation of automated backup process on one server and consequent restore on a different one (a test server, for example).

This method allows us to automate the backup copy creation process, to check the backup copies by restoring them and fine-tune the processes shown above.

Sources:

Backup
Restore
Backupset
CHECKDB
SHRINKFILE
sys.master_files

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov