Return
-
Title
How to set up a clean up maintenance plan for backups with different retention periods? -
Description
"We have one job for all our SQL Backups in LiteSpeed for SQL Server. Despite forcing all backups to be retained for a maximum of 4 months we still have an upward trend on backup storage usage.
Can we breakout the single task we have into different tasks (perhaps forcing differentials) and/or have a different maintenance plan to clean up old databases after 1 month, some after 3 months etc.?" -
Resolution
To clean up database back-ups which have different retention periods, one option is to organize backups in separate folders according to their retention period.First step is to create or modify a maintenance plan so that back-ups are backed up in different folders:
1) Go to Maintenance Plans2) Create or modify existing maintenance plan3) Add or modify Back Up tasks (“Fast Compression Backup” or “Back Up Database”), one for each retention period e.g. 1 months, 3 months, 6 months etc. by dragging them into the design space4) Double click on one of the backup tasks, name it with the retention period (e.g. 1 month) and select the connection5) Click on the ellipse icon beside the Databases field6) Click on “Selected databases” and select the databases which have the same retention period (e.g. 1 month)7) Verify other settings and finally click OK8) Click OK
Repeat this step for any database backup which has a different retention period.
The second step is to create “Clean Up Maintenance Plan” tasks for each associated back up tasks:1) Drag and drop the task into the main design area2) Double click on the task and set up the name, connection and “Delete File Types” option. Set the name to the retention period which is to be cleaned (e.g. 1 month)3) Under “File Location”, select “Select Search folder and delete files based on an extension” and select the backup folder which corresponds to the retention period4) Under “File Age”, select “Delete files based on the age of the files at task run time” and set the corresponding time period5) Click OK
Once you are back on the maintenance plan screen, double click on the bar under “Description” to set the Subplans and Schedule.
*****************************************************************************************************************Please ensure that you are selecting the correct File Age periods and backup folders when setting the maintenance plan to ensure that recent vital backups are not deleted.*****************************************************************************************************************