Creating Backup Jobs in SQL Server 2005

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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG, 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 task: bak 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'\\server02\dbbackup\sql2005', -- 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 3: Maintenance 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 operation: Constraint.
• Value: Success.
• 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 task: Backup 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.