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
- Open up SQL Server Management Studio
- In the Object Explorer pane, expand Management
- Right-click Maintenance Plans
- Select Maintenance Plan Wizard
- When prompted, provide a meaningful Name and Description for your plan
- 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
- Click Next
- Check Maintenance Cleanup Task, then click Next
Note: Highlighting a Task will show a description of what it does - If also performing other maintenance tasks, set the order in which they should be performed, then click Next
- 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
- Click Next
- Specify the location to save or send reports when the maintenance job completes, then click Next
- 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.