Maintenance plan "backup set will expire" setting not working

SQL Server Administration
1

I have a maintenance plan that runs one task, to backup a database. The "backup set will expire" setting is set to 4 days, yet it does not remove backups older then 4 days, it only removes backups older then 6 days. I've tried deleting the maintenance plan and associated jobs and recreating setting the "expire" days at 4 yet it keeps retaining the last 6 days of backups. Is the "Backup set will expire" setting for this maintenance plan stored in a table in MSDB?? I have looked for it yet cannot find it. Any suggestions on how to resolve this issue? Below is the t-sql for the plan. Thank you in advance for your assistance.

BACKUP DATABASE [Production] TO DISK = N'G:\DB-Backups\Production\Production_backup_2016_03_14_154014_1770759.bak' WITH RETAINDAYS = 4, NOFORMAT, NOINIT, NAME = N'Production_backup_2016_03_14_154014_1770759', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'Production' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Production' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''OnBase_Production'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'G:\DB-Backups\Production\Production_backup_2016_03_14_154014_1770759.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

2

I don't use the Maintenance Plans as they are such garbage (you'll find other Old Hands are unlikely to use them either ...) so I don't have experience to know the answer, but I wonder if the Maintenance Plan is built to delete files "Older than X days" rather than honouring the Expires After date in the individual backup files?

Minion Backup is a good, non-complex, alternative to maintenance plans.

1 Like
3

Thank you for your suggestion. I will take it into consideration but its unlikely that I will be allowed to use a 3rd party tool.

4

The expires after parameter is only utilized on backup devices (e.g. tape drives). This does not remove old backup files.

To remove old backup files you need to add the maintenance plan task which will remove old files.

1 Like
5

Thank you for your input. This is exactly what I was looking for. much appreciated.

6

[expiration_date] is stored in the MSDB meta data, so no reason why that can't be used to delete the file, but I can well believe that Maintenance Plans don't use it.

I'm sure its obvious to say, but the [expiration_date] by itself won't cause the file to be deleted, some batch process needs to come along, check the [expiration_date], and physically delete the file for anything past-due.