Intelligent Backups Revisited

In "Intelligent Transaction Log Backups," I presented an automated solution for backing up transaction logs. I've also developed a solution for automating differential backups of databases and a script that lets you quickly restore transaction logs from backups if the need should arise. With all three tools, backing up and restoring your SQL Server system is a snap.

The Differential Backup Solution

Like the transaction log backup solution, the differential backup solution uses an admin job and a worker job. The admin/worker job concept is simple. You schedule the admin job to run periodically. Each time the admin job executes, it creates or edits the worker job, then starts it. The worker job, which is updated every time the admin job executes, is responsible for performing the differential backups of databases.

In the differential backup solution, the admin job is a SQL Server Agent job named Server Daily Maintenance - Differentials. This job contains three steps:

The worker job will contain one step for each database. (You can choose to backup all databases or just databases in Full Recovery mode.) For each database, the worker job first checks to see whether a full backup exists. If a full backup doesn't exist, it performs one. If a full backup exists, it performs a differential backup. The backups can be performed using the SQL Server native format or LiteSpeed format. (LiteSpeed is a third-party utility from Quest Software.) The default backup format is SQL Server native, so LiteSpeed isn't required for this solution.

I created the SDM_Differentials_QuickSetup.sql script to quickly set up the differential backup solution. When you run SDM_Differentials_QuickSetup.sql, it creates the mnt_CleanUp and mnt_BackupDifferential stored procedures and the Server Daily Maintenance - Differentials admin job.

Note that the admin and worker jobs use xp_cmdshell when they run. I put xp_cmdshell inside a wrapper. That way, if you typically have xp_cmdshell disabled, the jobs will enable it for a brief moment so that they can perform their tasks, then return xp_cmdshell to the disabled state. If company policy forbids you to use xp_cmdshell even when it's in a wrapper, you won't be able to use this solution.

After you run SDM_Differentials_QuickSetup.sql, you can change some settings in the Server Daily Maintenance - Differentials admin job if desired:

The differential backup solution is a useful supplement to the transaction log backup solution. Another helpful supplement is the RestoreLogs.sql script.

RestoreLogs.sql

When you use the transaction log backup solution, you can conceivably have hundreds of transaction log backup files, many of which might have been created after different database backups. This can make it difficult to determine which transaction log backups go with which database backup. RestoreLogs.sql can solve this problem for you. It identifies which transaction log backups go with the latest full database backup and the order in which to restore the transaction log backups. And perhaps most important, it builds the restore commands for you.

To use RestoreLogs.sql, the first thing you need to do is locate the latest full backup file and obtain the FirstLSN, which is the log sequence number (LSN) of the first log record in the backup set. For data and differential backups, the FirstLSN identifies the earliest log record that's needed to perform a recovery. (You can read more about LSNs on the "Log Sequence Numbers and Restore Planning" web page.)

To find the FirstLSN for SQL Server native backups, you can use the command

RESTORE HEADERONLY  FROM DISK = 

where is a path to the latest full backup file, such as N'E:MSSQLBAKTestDB.bak'. For LiteSpeed backups, you can use the command

EXEC master.dbo.xp_restore_headeronly

  @filename =

After you have the FirstLSN, find the line

SET @FirstLSN = 19000000005900099

in RestoreLogs.sql and replace 19000000005900099 with that number.

Next, you need to specify where the transaction log backups are stored. Find the code

SET @backupdirectory =  N'E:MSSQLTRANTest'

and replace E:MSSQLTRANTest with your location.

Finally, you need to specify the name of the database you want to restore. Find the line

SET @DatabaseName = N'Test'

and replace Test with your database's name.

Once these modifications are made, you can run the script. The results will not only show the transaction log backups used but also give the appropriate restore commands.

RestoreLogs.sql is designed to work with the transaction log backups created by the transaction log backup solution. However, you can still use the script if you have an existing set of transaction log backups created by another process. You have two choices: Make the filenames compliant, or revise the code in RestoreLogs.sql.

For the first option, you need to make sure that the names of your transaction log backup files follow two conventions:

For the second option, you need to modify and/or exclude the filters described in the first option. This isn't difficult, but there's insufficient space to go into details here. I recommend using the first option if you have a handful of transaction log backup files and the second option when renaming the files would become too tedious and time-consuming.

A Complete Backup System

With the differential backup solution and the transaction log backup solution, you have a complete backup system that you can set and forget. And when you need to do a restore, RestoreLogs.sql can quickly find the appropriate transaction log backups and build the restore commands you need to use. You can download SDM_Differentials_QuickSetup.sql and RestoreLogs.sql by clicking the Download the Code Here button at the top of the article page. These scripts work on SQL Server 2008 and SQL Server 2005. (To download the script that creates the admin job for the transaction log backup solution, see "Intelligent Transaction Log Backups.")

In "Intelligent Transaction Log Backups" (November 2010, InstantDoc ID 125991), I presented an automated solution for backing up transaction logs. I've also developed a solution for automating differential backups of databases and a script that lets you quickly restore transaction logs from backups if the need should arise. With all three tools, backing up and restoring your SQL Server system is a snap.

The Differential Backup Solution

Like the transaction log backup solution, the differential backup solution uses an admin job and a worker job. The admin/worker job concept is simple. You schedule the admin job to run periodically. Each time the admin job executes, it creates or edits the worker job, then starts it. The worker job, which is updated every time the admin job executes, is responsible for performing the differential backups of databases.

      In the differential backup solution, the admin job is a SQL Server Agent job named Server Daily Maintenance - Differentials. This job contains three steps:

1.     The admin job first creates the msdb.dbo.SDM_Cleanup table if it doesn't exist. This table stores records on database backups that have been performed. Records are added to the SDM_Cleanup table by the worker job.

2.     The admin job calls the mnt_CleanUp stored procedure. As its name suggests, this stored procedure performs cleanup operations, including deleting old database backups. It also deletes the associated records in SDM_Cleanup.

3.     The admin job uses the mnt_BackupDifferential stored procedure to create a SQL Server Agent job named Maintenance_BackupDifferential. The admin job then executes Maintenance_BackupDifferential, which is a worker job.

      The worker job will contain one step for each database. (You can choose to backup all databases or just databases in Full Recovery mode.) For each database, the worker job first checks to see whether a full backup exists. If a full backup doesn't exist, it performs one. If a full backup exists, it performs a differential backup. The backups can be performed using the SQL Server native format or LiteSpeed format. (LiteSpeed is a third-party utility from Quest Software.) The default backup format is SQL Server native, so LiteSpeed isn't required for this solution.

      I created the SDM_Differentials_QuickSetup.sql script to quickly set up the differential backup solution. When you run SDM_Differentials_QuickSetup.sql, it creates the mnt_CleanUp and mnt_BackupDifferential stored procedures and the Server Daily Maintenance - Differentials admin job.

      Note that the admin and worker jobs use xp_cmdshell when they run. I put xp_cmdshell inside a wrapper. That way, if you typically have xp_cmdshell disabled, the jobs will enable it for a brief moment so that they can perform their tasks, then return xp_cmdshell to the disabled state. If company policy forbids you to use xp_cmdshell even when it's in a wrapper, you won't be able to use this solution.

      After you run SDM_Differentials_QuickSetup.sql, you can change some settings in the Server Daily Maintenance - Differentials admin job if desired:

·         By default, the Server Daily Maintenance - Differentials admin job is scheduled to run every six hours. If desired, you can change the frequency in the New Job Schedule page.

·         By default, the mnt_CleanUp stored procedure is set to delete database backup files that are older than one day. If you want to change that default, find the code

EXEC msdb.dbo.mnt_CleanUp
  @DaysOld = 1

in the second job step and replace 1 with an integer that represents the number of days you want the database backup files to be kept on the local server. Note that the stored procedure deletes only those backup files created by the transaction log backup solution.

·         By default, the worker job performs differential backups of databases in Full Recovery mode using the SQL Server native format. It stores the backup files in E:MSSQLDIF, overwriting any existing backup file for a database. If you want to change these defaults, find the code

EXEC dbo.mnt_BackupDifferential
  @mode = 'Full',
  @WriteOver = 1,
  @Format = 'NV',
  @dir = N'E:MSSQLDIF'

in the third job step. If you want to perform differential backups of all the databases, specify 'All' for @mode. If you don't want to overwrite existing backup files, specify 0 for @WriteOver. If you want to use the LiteSpeed format, specify 'LS' for @Format. If you want to store the backup files in a different location, replace E:MSSQLDIF with the desired directory in the @dir argument. The directory you specify must exist. The worker job will create subdirectories in that directory as needed, so the SQL Server service account must have write permissions to that directory.

      The differential backup solution is a useful supplement to the transaction log backup solution. Another helpful supplement is the RestoreLogs.sql script.

RestoreLogs.sql

When you use the transaction log backup solution, you can conceivably have hundreds of transaction log backup files, many of which might have been created after different database backups. This can make it difficult to determine which transaction log backups go with which database backup. RestoreLogs.sql can solve this problem for you. It identifies which transaction log backups go with the latest full database backup and the order in which to restore the transaction log backups. And perhaps most important, it builds the restore commands for you.

      To use RestoreLogs.sql, the first thing you need to do is locate the latest full backup file and obtain the FirstLSN, which is the log sequence number (LSN) of the first log record in the backup set. For data and differential backups, the FirstLSN identifies the earliest log record that's needed to perform a recovery. (You can read more about LSNs in the "Log Sequence Numbers and Restore Planning" web page at msdn.microsoft.com/en-us/library/ms190729.aspx.)

      To find the FirstLSN for SQL Server native backups, you can use the command

RESTORE HEADERONLY

  FROM DISK =

where is a path to the latest full backup file, such as N'E:MSSQLBAKTestDB.bak'. For LiteSpeed backups, you can use the command 

EXEC master.dbo.xp_restore_headeronly

  @filename =

After you have the FirstLSN, find the line

SET @FirstLSN = 19000000005900099

in RestoreLogs.sql and replace 19000000005900099 with that number.

      Next, you need to specify where the transaction log backups are stored. Find the code

SET @backupdirectory =

  N'E:MSSQLTRANTest'

and replace E:MSSQLTRANTest with your location.

      Finally, you need to specify the name of the database you want to restore. Find the line

SET @DatabaseName = N'Test'

and replace Test with your database's name.

      Once these modifications are made, you can run the script. The results will not only show the transaction log backups used but also give the appropriate restore commands.

      RestoreLogs.sql is designed to work with the transaction log backups created by the transaction log backup solution. However, you can still use the script if you have an existing set of transaction log backups created by another process. You have two choices: Make the filenames compliant, or revise the code in RestoreLogs.sql.

      For the first option, you need to make sure that the names of your transaction log backup files follow two conventions:

·         The database's name must be in the filename.

·         The filename needs to end in either _NV.trn for native backups or _LS.trn for LiteSpeed backups.

      For the second option, you need to modify and/or exclude the filters described in the first option. This isn't difficult, but there's insufficient space to go into details here. I recommend using the first option if you have a handful of transaction log backup files and the second option when renaming the files would become too tedious and time-consuming.

A Complete Backup System

With the differential backup solution and the transaction log backup solution, you have a complete backup system that you can set and forget. And when you need to do a restore, RestoreLogs.sql can quickly find the appropriate transaction log backups and build the restore commands you need to use. You can download SDM_Differentials_QuickSetup.sql and RestoreLogs.sql by going to www.sqlmag.com, entering 128818 in the InstantDoc ID text box, clicking Go, then clicking the Download the Code Here button. These scripts work on SQL Server 2008 and SQL Server 2005. (To download the script that creates the admin job for the transaction log backup solution, see the article "Intelligent Transaction Log Backups.")