Create SQL2005 automatic backups, regularly deleted maintenance plans

The Daily tasks are:

1 complete backup or differential backup of the database on a regular basis.

2 regularly clean up the backup files, because the storage space is limited, you may need to save only a period of time files (such as a week or January).

And how do you do these two points? The author believes that it is possible to implement this function before the SQL SERVER2005 version comes out, but it is not straightforward to implement regular cleanup of backup files, which typically requires handwriting batch job maintenance, and in SQL Server 2005 version, These features have been designed to be prefabricated components, just drag and drop in the data maintenance plan and set up the related properties, and the related steps are as follows:

First step: New maintenance Plan

Figure 1

Right-click the new maintenance plan in the Server Management maintenance plan.

Step Two: Design maintenance plan

2.1 Drag the Backup database task from the Task Toolbox in the maintenance plan to the Maintenance Plan designer.

2.2 Drag the Purge history task from the Task Toolbox in the maintenance plan to the Maintenance Plan designer.

2.3 Drag the Cleanup maintenance task from the Task Toolbox in the maintenance plan to the Maintenance Plan designer.

As shown in the following illustration:

Figure 2

Right-click to edit the backup database task to set properties, as shown in the following figure:

Figure 3

This task will automatically complete the full backup job for the database.

Right-click to edit the Purge history task, as shown in the following figure:

Figure 4

This task automatically completes the Purge history job.

Right-click to edit the "Clean Maintenance" task, as shown below:

Figure 5

This task automatically completes the job of purging historical backup files.

The job schedule properties for this maintenance plan task are set as follows:

Figure 6

This step completes the setting of the job properties. Maintenance jobs are run on a regular basis by the system.

Step three: Save the maintenance plan

To do this, you can track the completion of this plan by simply viewing the log records, and you can adjust and modify the maintenance plan according to the success record.

Start SQLServerAgent with Xp_servicecontrol (SQL Server Agent)

EXEC master.dbo.xp_servicecontrol ' start ', ' SQLServerAgent '