There is option for this in Maintenance Plans. You will need to add task named "Maintenance Cleanup Task" into maintenance plan (or create a separate maintenance plan for cleanups) and specify the search criteria and file age. I hope this will solve your purpose.
Kindly mark the reply as answer if they help
- Click the "Maintenance Plan" button and select "Maintenance Wizard" to start the cleanup scheduler. Click "Next" on the first welcome window.
- Type a name in the "Name" text box. This is any friendly name that you recognize for your cleanup task. Click "Next." Select "Maintenance Cleanup Task" as the schedule task type and click "Next."
- Select "Backup files" in the "Delete files of the following type" section. Enter the location for your SQL Server backup directory and then enter "bak" in the "File Extension" text box. Click "Next."
- Check the box for "File Age" and enter the age of the backup files you want to delete. For instance, if you want to keep 2 days of backups and delete all older backup files, enter "2 Days" in the section labeled "Delete files older than the following." Click "Next."
- Review the changes and settings you've made and click "Finish" to delete the backup files on your hard drive.
I have three different maintenance plan weekly_db_1, weekly_db_2 & weekly_db_3 all these plans included different databases.
& need to delete older backup files (retention period ex: 28 days) Suppose i will use maintenance clean up task. then my doubts are
1. do i need to set Maintenance cleanup task for each of my backup maintenance plan?
2. In clean up task the option is provided a bak as a file name. then suppose my backup plan fails, so will it be deleted my older files?
3. because i need is, my cleanup task will run only when my backup completed successfully.
Guys pls help me as i am new DBA.
To avoid confusion:
1. Set each weekly maintenance plan to use a different backup folder e.g. x:\sqlbackups\weekly_db_1,x:\sqlbackups\weekly_db_2,x:\sqlbackups\weekly_db_3.
2. Set a success constraint between the backup and backup cleanup task in each maintenance plan.
This should keep each maintenance plans backups separate and the associated backups of the plan are not removed unless the maintenance plan backups are successful.