Creating Backup Jobs in SQL Server 2005

It is not necessary to create a Maintenance Cleanup Task in the transaction log backup plan in case you stored both the database and the transaction log files in the same folder. Instead, you can reuse the Maintenance Cleanup Task from the database backup plan by modifying the file extension parameter from bak to * (asterisk).

A differential database backup was not included in the SQL 2000 maintenance plan. Additional efforts were required in order to add a differential backup job and complete the database backup schedule. Now it is much easier. The Back up database task allows you to schedule a differential database backup the same way as a full database backup. I would recommend storing differential backup files in the same folder with full database backup files so you can use the same cleanup task to delete all of them.

You can test these plans/jobs the same way as in SQL 2000: just right-click on the plan icon in Object Explorer and select Execute from the dropdown menu, or right-click on the job and select Start Job at Step…. Please note that if you execute a maintenance plan, the status could be Success even if the job has actually failed (see Figure 5), or vice versa, but the job always returns a correct status message (see Figure 6).

Figure 5: Success status message.

Figure 6: Failure status message.

Checking the results

The plan/job results will be stored in the msdb database and in the file specified in the plan Logging property. If you checked the Log extended information box in the Reporting and Logging window during maintenance plan creation, the detail information will be stored in both the log file and the msdb.dbo.sysmaintplan_logdetail table. If you left this box unchecked and the job status is Succeeded, then there are no details provided in the log file or the msdb.dbo.sysmaintplan_logdetail table. However, if any of the tasks failed you can find all the details there.

You can also check the results in Management Studio by right-clicking on the plan or job icon and selecting View History from the dropdown menu, then expanding the record for a failed plan/job.

Figure 7: View History example.

If you work in a multi-server environment and you need to automate the review and analysis of the backup results, you can use the intended stored procedures, or query the appropriate tables and views directly.

The sp_help_jobhistory procedure can check the history of the job regardless of the way the job was created: either using modified SQL Server 2000 scripts or scripts generated by SQL Server 2005 Server Management Studio. Here is the example:

EXEC msdb.dbo.sp_help_jobhistory
     @job_name = N'Pubs_NW_tl_backup',
     @mode = N'FULL';

You can also query the msdb.dbo.sysmaintplan_plans view and the msdb.dbo.sysmaintplan_subplans, msdb.dbo.sysmaintplan_log, or msdb.dbo.sysmaintplan_logdetail tables to obtain maintenance plan history. For example, the following query returns information about a failed Pubs_NW_tl_backup maintenance plan:

SELECT mp.[name],
     msp.subplan_name,
     mpl.start_time,
     mpl.end_time,
     mpld.command,
     mpld.error_message
FROM msdb.dbo.sysmaintplan_plans mp
JOIN msdb.dbo.sysmaintplan_subplans msp ON mp.id=msp.plan_id
JOIN msdb.dbo.sysmaintplan_log mpl ON msp.subplan_id=mpl.subplan_id
JOIN msdb.dbo.sysmaintplan_logdetail mpld ON mpl.task_detail_id=mpld.task_detail_id
WHERE mp.[name]=N'Pubs_NW_tl_backup' AND mpl.succeeded = 0
ORDER BY mpl.start_time DESC;