Implementing Automated Database Backup and Recovery with built-in Tools

Introduction

There are quite a lot of examples on the web for creating database backups and recoveries. We are going to provide another example of the built-in tools in MS SQL Server.

In this example, we collected several approaches, including the database integrity check before creating a backup and restore it from the previously created backup.

Solution

First, we are going to provide an algorithm for creating a backup:

  • Determine the databases you need to make a backup for
  • Check every selected database for integrity
  • Create a backup for each selected database (full or differential, or transaction log)
  • Check the received backups
  • Reduce the transaction logs of the used databases in size (if necessary)

Then, provide an example of implementing the algorithm described above.

To determine the databases you need to back up, create the following table:

The table of backup settings

The first column specifies the database identifier, and FullPathBackup contains a full path to create complete backups (for example, ‘drive: \ … \’). DiffPathBackup and LogPathBackup contain complete paths to create differential and transaction log backups respectively. If the DiffPathBackup or LogPathBackup column is empty, the database will not be involved in creating the differential backup or the transaction log backup respectively.

In addition, it is possible to create a view based on this table:

The view to set up backups

This view allows us to quickly see what databases are involved in the backup process.

Now, create a view that displays information on database files of the sys.master_files system view.

The view with database files

Code

To create complete backups, implement a stored procedure:

Code

As you can see, this stored procedure resolves all the remaining points of the algorithm to create complete backups.

In a similar way, it is possible to implement stored procedures to create differential backups and backups of transaction logs:

The procedure to create differential database backups:

Code

The database integrity check is quite a resource-intensive task. Thus, it is not necessary to check database integrity before creating differential database backup to increase performance.

The procedure to create transaction log backups

Code

Transaction log backup is created very often, and checking database integrity is quite a resource-intensive task. Therefore, we do not usually check database integrity before creating a transaction log backup.

In addition, it should be noted that it is necessary to make full database backups of master, msdb, and model from time to time.

To automate the process of creating backups, it is enough to add a call of the implemented stored procedures to Windows Task Scheduler, Agent Task or any available service.

It is necessary to select the frequency of calls for each stored procedure individually, based on the peak load, downtime, etc.

The most common approach is as follows:

1) Create a full backup once a day
2) Create differential backups every 2-4 hours
3) Create transaction log backups every 5 to 60 minutes

It is also important to remember that databases are usually involved in a system of fault tolerance and quick availability. If you use backups of transaction logs in the latter, it’s important not to interfere with this process (you cannot allow backups of the database transaction log by different processes, so the sequence for recovery from these backups will be lost).

Here, we provided examples of processing each database sequentially. However, it is quite possible to parallelize processing in production, making several backups simultaneously. This can be done in many ways. For example, by calling the following stored procedure:

The procedure of asynchronous query call:

Code

Here, asynchronous behavior is achieved by the dynamic creation of the Agent tasks, as well as by their execution and deletion.

Let’s provide a general algorithm to restore a database from the previously created backups (in another or test environment):

1. Determine what databases must be restored, and the place where backups are stored

2. Restore databases

3. Check the integrity of recovered databases

Here is an example of implementing an algorithm to restore a database from the full backup. For the differential one, the procedure is similar, but first, a full backup is restored, and then the differential one. To determine what databases must be restored, and the place where backups are stored, create two tables:

Table to set database recovery

Here, the column values are the same as the column values for the [srv]. [BackupSettings] table, with the only difference that existing backups will be created for recovery using the full path. 

Table of database files for recovery

We need this table to determine full file names of the database to be restored for later migration (for example, [SourcePathRestore]=’Logic file name’ and [TargetPathRestore]= ‘drive:\…\Physical file name’, and [Ext]=’File extension’.

Actually, we can determine the logic database file names for the following query:

Getting logic database file names:

Information on backups stored in the file can be retrieved in the following way:

Getting information on database backups

Provide an example of implementing a stored procedure for database recovery from the full backup and check them for data integrity:

Procedure to restore database by full backups

Code

To determine which full backup must be restored, we take the file name formed in the following way:

<database name>_Full_backup_<year>_<number of the month>_<number of the day in the month>.bak

To automate the process for database recovery from the full backups, it is necessary to add a call of the implemented stored procedure to the Windows Task Scheduler, Agent Task or any available service.

We can view the latest database backups using the following view:

View on the latest database backups:

Code

Summary

In this article, we explored the example of implementing the automated backup process on one server and later restoring it on another one (for example, test server).

This method allows automating the process of creating backups, checking backups with the recovery method, and customize the discussed processes to your needs.

References:

Evgeniy Gribkov

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
291 views