Categories
DBA

Backup Maintenance Plan with Cleanup task

This article provides a complete description about configuring backup using maintenance plan including cleanup task. This will be useful to junior DBA’s and also for the persons who have no voice in SQL server. This article is applicable for both SQL 2005 & SQL 2008.

Step 1: Connect to the SQL server and then expand the Management folder and right click on the Maintenance Plan Wizard as shown below

backup_0

Step 2 : Once you done, the maintenance plan welcome screen will appear, click next to continue

backup_1

Step 3 : In the screen below you can provide the Maintenance plan name. There is another option which is used to confirm whether a single maintenance plan should hold all the maintaining activity or separate plan for each maintenance activity. In this case I’ve chosen Separate maintained plan for easy of use.

backup_2

Step 4: You can select any of the backup types, i.e Full or Differential or Transaction Log as show below. In this case I’ve selected Differential backup. Click next to proceed

NOTE: Filegroup backups are not included in maintenance plan, hence you need go for script option.

backup_3

backup_4

Step 5: While configuring backup you have option to select which database , destination of backup and schedule for backup. If you need all the databases to be backed up click on the Database(s) drop down menu and select “All Databases” option and specify the destination either disk or tape.

backup_5

Step 6: Once its done, click on Change button to specify the backup schedule. After clicking on the change button you will get a wizard as show below. Specify you schedule over there.

backup_6

Step 7: In the below screen you have the option to save the backup log or to send the report to an email operator, choose your need.

backup_7

Step 8: Once everything done, you will be shown with the summary screen, click on finish the maintenance plan will be created.

backup_8

backup_9

Step 9: You can see the backup maintenance plan created below. If you need to go for cleanup task proceed with the remaining steps or else end with this step it self.

Cleanup task will help you to delete old backup files as per the retention period you have given. Double click on the backup maintainence plan to open it.

backup_10

Step 10 : In the left pane you can see Maintenance cleanup task option, just drag that to the right pane. Once done right click on the task, you will be shown with a menu, select edit from the menu.

backup_11

Step 11 : You will be show with a wizard as shown below, provide the servername, select backup files and provide the backup path (which is given in step 5) and the extension of bakup file (which is given in step 5) and finally the retention period. After providing the detail click on the OK button.

backup_12

Step 12 : Once its done right click on the backup maint plan and click on the “Save selected items” option as shown below. That’s it you have configured backup plan with clean up task.

backup_13

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*