Question

How do I create a SQL maintenance plan to remove old backup files?

 

Important

You should consider space needed and acceptable losses when determining a SQL backup retention policy.

 

Answer

  1. Open up SQL Server Management Studio
  2. In the Object Explorer pane, expand Management
  3. Right-click Maintenance Plans
  4. Select Maintenance Plan Wizard 
  5. When prompted, provide a meaningful Name and Description for your plan
  6. If you would like this plan to run on a regular schedule, rather than On Demand, click Change to specify when the plan should run
  7. Click Next
  8. Check Maintenance Cleanup Task, then click Next
    Note: Highlighting a Task will show a description of what it does
  9. If also performing other maintenance tasks, set the order in which they should be performed, then click Next
  10. Select to delete Backup files, specify the location and file extension of your backups, and specify the age at which those backup files should be deleted
    Note: Full SQL backups typically use the BAK extension while Transaction Log backups typically use the TRN extension
  11. Click Next
  12. Specify the location to save or send reports when the maintenance job completes, then click Next
  13. Review the settings for the maintenance plan, then click Finish



 

Overview

The purpose of this article is to provide the basic steps of creating a maintenance task within SQL Server which is crucial to the success of heavily coded or large databases. It is recommended to run this in accordance with your maintenance plan. A maintenance plan is suggested in our specifications guide. For a custom maintenance plan please contact our services department.