Intelligent Transaction Log Backups

Designing a backup strategy for transaction logs is simple in concept. However, various scenarios can cause the design to be more complex than anticipated. For example, what if you have a mirrored database that automatically fails over as the principal database? Does your transaction log backup job start automatically? Does the job stop when the principal database becomes the mirror? What if you change the recovery model of a database? Will your transaction log backup job adjust automatically?

I have developed a simple solution that works on SQL Server 2008 and SQL Server 2005. It uses what I call the admin/worker job concept. The admin job is the only job that's scheduled. By default, it's scheduled to run every 15 minutes. (You can change that default if desired.) The admin job creates/edits and starts the worker job. (The worker job is never manually created or scheduled to run.) The worker job is updated every time the admin job executes.

The combination of the admin job and worker job lets you use this solution on any box and never have to make any modifications. It will intelligently execute as needed, no matter whether the database is a principal or mirror in Simple or Full Recovery mode. The admin job will identify the state of the database and act accordingly.

The mnt_BackupLog stored procedure provides the "intelligence" in the admin job. It locates candidate databases—that is, online databases that are in recovery mode. When candidate databases are present, mnt_BackupLog creates and executes a worker job that will back up the transaction logs in those databases. (Database snapshots and databases that are in single-user or standby mode aren't considered candidate databases. Also, any databases that are part of log shipping are excluded.)

Let's take a detailed look at the admin and worker jobs. Then, I'll explain how to create the admin job using the SDM_Logs_QuickSetup.sql script.

The Admin Job

Figure 1: Outline of the job steps in the admin job (click to enlarge)

The admin job is a SQL Server Agent job named Server Daily Maintenance - Logs. Figure 1 outlines the job steps, the first of which is creating the msdb.dbo.SDM_Cleanup table if it doesn't exist. This table stores records on transaction log backups that have been performed. Records are added to the SDM_Cleanup table by the worker job.

The second job step calls the mnt_CleanUp stored procedure into action. This stored procedure determines which transaction-log backup files should be deleted using the records in the SDM_Cleanup table, then deletes them. It also deletes the associated records in SDM_Cleanup. If a backup file has already been deleted, mnt_CleanUp still removes the associated record.

The last job step locates the candidate databases, then uses the mnt_BackupLog stored procedure to create a worker job that will back up the transaction logs for those databases.

The Worker Job

Figure 2: Sample job step from a worker job (click to enlarge)

The worker job is a SQL Server Agent job named Maintenance_BackupLog. It will contain one step for each candidate database. So, if there are three candidate databases, there will be three job steps. Figure 2 shows a sample job step.

Here's what happens in each job step. The worker job first checks to see whether a full backup of the database exists. If not, it will perform one. It then backs up the transaction log using either 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.

After the transaction log is backed up, the worker job writes the backup's fully qualified filename, type of backup, and database name to the SDM_Cleanup table.

Getting Started

I created SDM_Logs_QuickSetup.sql to quickly set up this solution. (You can download this script by clicking the Download the Code Here button near the top of the page.) When you run SDM_Logs_QuickSetup.sql, it

Note that SDM_Logs_QuickSetup.sql and the two stored procedures use xp_cmdshell when they run. I put xp_cmdshell inside a wrapper. That way, if you typically have xp_cmdshell disabled, the script and stored procedures will enable it for a brief moment so 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_Logs_QuickSetup.sql, you can change some settings for the Server Daily Maintenance - Logs admin job if desired:

Just Set and Forget

After you have the admin job as you like it, you're all set and you can forget about it. The Server Daily Maintenance - Logs job will have your transaction log backups covered.