SQL Server 2005 transaction log backup maintenance plan via SSIS

By: Michelle Gutzait   |   Updated: 2009-05-19   |   Comments (5)   |   Related: > Maintenance

Problem

In SQL Server 2005, when you configure a transaction log backup maintenance task for "all user databases", the transaction log backup task is not automatically checking the Recovery Model of the databases configured to be backed up. The result is that if the recovery model of the database is Simple, the task will simply fail.  In SQL Server 2008 this issue has been resolved and only the databases with FULL recovery model will have their transaction logs backed up.  One common solution to this "issue" in SQL Server 2005 is coding the transaction log backup task.  To me, this is like inventing the wheel (I am the lazy DBA of the gang), so I would like to provide a much simpler solution here, which will also allow reverting back to the defaults after upgrading the instance to SQL Server 2008.

Solution

So I already have a maintenance task configured to backup T-Logs of all my "user databases" and it occasionally fails when a database is in a simple recovery model. Right now, I simply check the job's log occasionally and ignore failures.  Let me show you a simple way to add a step to your existing maintenance plans to resolve this issue until you upgrade to SQL Server 2008.

My maintenance task looks like this:

General configurations:

• Backup task:
  • Backup type: Transaction Log
  • Databases: All user databases
  • Destination: disk
• Cleanup of database backup files
  • Older than one day
• Scheduled to run every 1 hour

Unfortunately, the first step fails if a database is added with a SIMPLE recovery model or if an existing database is converted from FULL recovery model to SIMPLE.

My new maintenance plan looks like this:

I modified the precedence constraint between the tasks to be executed on COMPLETION instead of on SUCCESS.  With this simple change, I make sure that the maintenance cleanup task always executes.

The Checkup Step

Next I added a step in my SSIS Package that I am calling the "The Checkup Step". This new step will validate all of the transaction log backups that should have been completed were actually successful.  Here is the the sample code:

declare @i int;
with x as 
(   
select max(isnull(datediff(mi,b.backup_start_date,getdate()),100)) as NumHrsLastBackup,
 d.name as DatabaseName   
from master..sysdatabases d with (nolock)   
left join msdb..backupset b with (nolock)
 on  d.name = b.database_name 
 AND b.type = 'L'   
 -- We are checking only the last backup:                
 and b.backup_start_date = (select max(backup_start_date)
     from msdb..backupset b2
     where b.database_name = b2.database_name 
     and b2.type = b.type)   
-- Check only user DBs with SIMPLE recovery model, ONLINE and not READ ONLY:   
where d.name NOT IN ('tempdb','msdb','master','model')     
and DatabasePropertyEx(d.name,'Recovery') = 'FULL'     
and DatabasePropertyEx(d.name,'Status') = 'ONLINE'     
and DatabasePropertyEx(d.name,'Updateability') = 'READ_WRITE'   
group by d.name, b.type, b.backup_size)

-- How many databases were not backed up in the last hour 
-- (change the '>59' to the right schedule for your job, in minutes)
select @i = count(*) 
from x 
where NumHrsLastBackup > 59; 
-- Fail the step if backups that should exist, do not exist
IF @i > 0 raiserror ('Error: T-Logbackup failed for some databases',16,1);

I tried to add "The checkup step" to the Maintenance Plan itself as follows:

Executing it on failure of the Backup Task.  Unfortunately, in order to avoid package failure when the Database backup task fails, I tried to set up the Properties of the Backup Database task to the following:

• FailPackageOnFailure --> False
• FailParentOnFailure --> False
• MaximumErrorCount --> 0

Unfortunately, these configurations did not work and the package kept failing.  So I had to find another option.

Add "The checkup step" to the job

Based on the failures, I decided to add "The checkup step" to the SQL Server Agent Job that was created by SSIS when the schedule was established.  The original Job Steps look like the following:

The first step to modify the SQL Server Agent Job is to click the "Edit" button on the bottom of the interface.  Then click the Advanced options on the left.  Finally, change the "On failure action" to "Go to next step" and retain the "On success action" of "Quit the job reporting success" as shown below.  Click the "OK" button to save the settings.

Now let's click the "Insert" button to add a New step after the existing one.  On the General page configure the following:

• Step name: Check missing TLog Backups
• Type: Transact-SQL script (T-SQL)
• Database: Master
• Command: Script from above

On the Advanced page retain the On success action and On failure action defaults.

Although it is not a best practice to modify SQL Server Agent Jobs that are created by SSIS, I found this as a reasonable means to correct my issue in SQL Server 2005.  Now, the job will only fail if the backup of one of the databases in FULL recovery model fails.

Here is an example of the execution from the job history where the job itself succeeded although the step failed.

Summary

This tip provides a simple solution for the problematic T-Log backup task in SQL 2005 in SSIS.  After you upgrade your instance to SQL Server 2008, you can just remove the additional step from the job and you are good to go. On a second thought, if you leave "The checkup step" in the job it will simply provide you with another check that the backups were successful.

Next Steps

• Be sure to understand the database recovery models as you perform your backups, which can be accomplished by reviewing the Select the SQL Server database recovery model to ensure proper backups tip.
• Learn a different approach to perform transaction log backups by reviewing Transaction Log Backups for All SQL Server Databases where all of the backup operations are scripted.
• Design your complete backup and Disaster Recovery solution with the Backup Checklist.
• Learn how to take full and transaction log backups in between your regularly scheduled backups without affecting the sequence of files that would need to be restored, with the COPY_ONLY option in SQL 2005.
• Solve issues with very large Transaction Log backups.
• Start planning your upgrade to SQL 2008 and check out Explore Database Backup Compression in SQL Server 2008.

About the author

Michelle Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips