SQL Server 2005 transaction log backup maintenance plan via SSIS

By:   |   Comments (5)   |   Related: > Maintenance


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.


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

ExisitngMaintenancePlan 1

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:

TheJobBefore 1

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.

OnStepFailure 1

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

TheNewStep 1

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

OnFailureNewStep 1

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.



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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Michelle Gutzait 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

Comments For This Article

Friday, March 20, 2015 - 12:45:12 PM - alemayehu Back To Top (36612)

It would have been good if you have attached the detail error message. However the possible reason could be


1. The SQL server agent is not running


2. The Job owner might not have the right privilege to execute the job. Try to change the job owner to SA


3. Make sure the job is scheduled. (After creating the job make sure it is scheduled to run at a given time)


Wednesday, July 31, 2013 - 10:47:17 AM - itohan Back To Top (26072)

Good day. i am a DBA i set up a maintainance plan on sql server 2008 and its not executing . i have deleted and re created it still the same status . pls i do not know what else to do and how to get it up, all the jobs are failing.

Thursday, May 28, 2009 - 9:17:58 AM - tosscrosby Back To Top (3463)

I totally agree with creating your own scripts. Many people I know are very much against maintenance plans. I've had few issues with them but scripts are easy to copy/paste across servers and just seem to lend themselves to a great deal more flexibility - IMO. 

Wednesday, May 27, 2009 - 1:36:09 PM - jerryhung Back To Top (3457)

I totally agree SQL is stupid in some simple ways, such as Maint. Plan will try to backup OFFLINE databases, or do TLOG backup for SIMPLE recovery mode databases, if you select "All DB" or "All User DB" etc..

You would think it's easy to check this on every Maint. Plan execution

Although I beg to differ, with the amount of work you did, I almost can just create my own TLOG backup job and call my own script (or find one online) to do backup (e.g. my script excludes SIMPLE mode and OFFLINE db)


@DBName = '*'

@BackupType = ' TLOG'

Friday, May 22, 2009 - 7:02:56 AM - tosscrosby Back To Top (3421)

I beg to differ. When I created my maintenance plan I selected all user databases and there was an informational message on the task stating databases in simple mode would be excluded. I just moved to SQL 2005 within the last 2 months and I'm on sp3 so maybe that's been recently added in a service pack?

get free sql tips
agree to terms