SQL Server 2005 – Creating Backup Jobs

If you are going to use new Maintenance Plan features, I strongly recommend installing SQL Server 2005 Service Pack 1 first. Among the new features and improvements that are included in SQL Server 2005 Service Pack 1, there is a fix for the previously existing discrepancy between Back up database task, which allowed storing database backups in separate folders, and Maintenance Cleanup Task, which could not delete backup files from those subfolders.

In order to create a new maintenance plan in Management Studio, first connect to the target server using Windows Authentication, then right-click on the Maintenance Plan folder in Object Explorer, select New Maintenance Plan, and enter the plan name. As a result, a Maintenance Plan design panel will appear on the right, and a toolbox with available Maintenance Plan Tasks will be displayed on the left. Click on the Connection button to verify that the current connection uses Windows Authentication (recommended by Microsoft). Currently connected Windows user will become the owner of the job created by this maintenance plan.

The first step in creating a database or transaction log backup is to drag and drop Back up database taskfrom the toolbox to the design panel. Then double-click on that item to set the following necessary properties:

  • Databases: Click on the dropdown field to bring up the database selection window. For this example, I chose Northwind and Pubs as Figure 1 shows:
Figure 1: Database selection window.
  • Backup type: choose Full.
  • Destination parameters:
    • Back up to — choose Disk.Make sure that the Create a backup file for every database option is selected and the Create a sub-directory for each database box is checked.
    • You can use the default destination folder or specify your own. For this example, the network folderserver02dbbackupsql2005 has been selected.
    • Backup file extension — make sure that its value is bak without a leading dot.
    • Check the Verify backup integrity box.

When you are done, the Back up database task properties window should look like the one shown on Figure 2.

Figure 2Back up database task properties window.

Having verified the task properties, click OK. Now you can create a schedule for this plan. Click on the  button located to the right of the Schedule field and set the necessary parameters in the Job Schedule Properties window. Schedule name will be defaulted to <maintenance_plan_name>-Schedule and can be modified if necessary.

The other parameters are similar to SQL Server 2000 schedule setup. As you can see in Figure 4, the backup has been scheduled to run every day at 1 a.m. Click OK to save this schedule.

When you click on the Logging button, the Reporting and Logging window appears. It is similar to the SQL 2000 Reporting tab: the default folder for the backup log is C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG, and it can be modified. You can also choose between the Create a new file option that will create a new log file named <maintenance_plan_name> _yyyymmddhhmm.txt for every completed job, and the Append to file option (in this case you have to name the log file). You can also check the option to send an e-mail to the operator.

Unlike SQL Server 2000, there is no option in the Back up database task for removing database backup files from the backup folder. In order to delete obsolete backup files, an additional Maintenance Cleanup Task must be inserted and configured. Drag and drop this task into the design panel and double-click on it to set the properties:

  • Connections: keep the default Local server connection.
  • Delete files of the following types: choose the Backup files option.
  • File location: choose Search folder and delete files based on an extension and provide the same full path to the backup folder as you did on the Back up database task.The file extension also should be the same as that used in Back up database taskbak for the database backup files or trn for the transaction log backup files (both without a leading dot). If you created a separate directory for each database in Back up database task, you can also check the Include first-level subfolders box.
  • File age: check Delete files based on the age of the file at task run time and set the Delete files older than the following option. The smallest retention period is 1 day now, no more hours and minutes as it was in SQL 2000. If you click on the View T-SQL button, you will see a new undocumented stored procedure, xp_delete_file, with the following parameters:
EXECUTE master.dbo.xp_delete_file
0, — delete files
N’server02dbbackupsql2005′, — full path to the main directory
N’trn’, — file extension
N’08/08/2006 13:29:51′, — delete files created before this timestamp
1 — Including first-level subfolders

After the properties have been specified, the Maintenance Cleanup Task properties window should look like this:

Figure 3Maintenance Cleanup Task properties window.

The Maintenance Cleanup Task should be executed only upon successful completion of the Back up database task. To ensure this, we have to link both tasks with a constraint. Click on the first task and then drag an available component output (depicted as a green arrow) to the next task. Double-click on the arrow to check and edit, if necessary, the Precedence Constraint properties:

  • Evaluation operationConstraint.
  • ValueSuccess.
  • Multiple constrains: By default, it is Logical AND. In this case, it does not matter because we only have one constraint.

Now we are done with all the tasks and the design panel should look like the one shown in Figure 4:

Figure 4: Design panel example.

Click on the Save All icon to save the plan. As a result, a new job, with the same name as the maintenance plan name, has been created and you can see both the plan and the job in the Object Explorer panel.

We can create another maintenance plan for s transaction log backup. Before we do this, let us clarify the existing maintenance plan name by changing it from Pubs_NW_backup to Pubs_NW_db_backup. Right-click on the plan icon, select the Rename option, edit the plan name, and press Enter. The corresponding job will be renamed too.

Before you create a plan for a transaction log backup, you need to verify that the target database recovery model is either Full or Bulk logged.

Creating this plan is similar to the full database backup plan creation. There are only two different parameter values for Back up database taskBackup type should be set to Transaction Log, and Backup file extension should be set to trn; also, for Maintenance Cleanup Task the File extension parameter should be set to trn. Schedule this plan to run every several hours during business hours and save it.

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 theMaintenance Cleanup Task from the database backup plan by modifying the file extension parameter frombak 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 resultsThe 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 themsdb.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 selectingView History from the dropdown menu, then expanding the record for a failed plan/job.

Figure 7View 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;

Useful Tip

If you need to run an ad-hoc backup of your database without impacting the backup sequence, you can do it using the COPY_ONLY option:

BACKUP DATABASE AdventureWorks 
TO DISK = ‘server02dbbackupsql2005AdvWorksData.bak’ 
WITH COPY_ONLY;

Conclusion

Even though your existing SQL Server 2000 scripts can be helpful in jump-starting your database backup development in Server 2005, I would still recommend gradually moving to the 2005 methodology. Nevertheless, both methods currently work, and if this article helped you simplify database backup implementation, then it has done its job.

SOURCE

LINK

LANGUAGE
ENGLISH