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
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
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
- Creates the mnt_BackupLog stored procedure
- Creates the mnt_CleanUp stored procedure
- Creates the Server Daily Maintenance - Logs admin job.
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:
- By default, the Server Daily Maintenance - Logs job creates the SDM_Cleanup table in the msdb database. I like using that database because I know it's on every server and its data is groomed regularly. If you prefer to create it in a different database, you can change each instance of msdb.dbo.SDM_Cleanup to reflect the desired database in the first job step as well as in the mnt_BackupLog and mnt_CleanUp stored procedures that were created.
- By default, the Server Daily Maintenance - Logs job is scheduled to run every 15 minutes. You can change the desired frequency in the New Job Schedule page.
-
By default, the mnt_CleanUp stored procedure is set to delete transaction log backup files that are older than 1 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 transaction log backup files to be kept on the local server. -
By default, the mnt_BackupLog stored procedure has the worker job back up the transaction logs using the SQL Server native format and store the backup files in E:\MSSQL\TRAN. If you want to change these defaults, find the code
EXEC msdb.dbo.mnt_BackupLog @Format = N''NV'', @dir = N''E:\MSSQL\TRAN''
in the third job step. If you want to use the LiteSpeed format, change NV to LS in the @Format argument. If you want to store the backup files in a different location, replace E:\MSSQL\TRAN 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.
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.
