| Downloads |
|---|
| 125991.zip |
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.
