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

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

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.

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.

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.

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.

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.

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

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.

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.

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.

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.