What is the behavior of SQL Server Maintenance Plan - Clean Up task for deleting old backups This seems pretty obvious, but it's remarkably hard to find an actual document that addresses this.

The scenario:


Full Backup - Weekly (Set to expire after 7 days)
Differential Backup - Daily (Set to expire after 7 days)
Transaction Log Backup - Hourly (set to expire after 3 days)


In this scenario there is a full backup and then 6 days of differential backups tied to the full backup, with several transaction logs in between.

Now, I want to create a cleanup task to remove old backups. If I set the cleanup task to remove backup files (bak) older than 7 days does it delete the full backup and essentially orphan the 6 differential backups?

Does the expiration policy on the backup task "lock" those files from being deleted before they expire? In essence, could I set the cleanup task to delete files older than 1 day, and yet not delete backup files that haven't yet expired?

The goal here is to do a couple of things:


If a FULL backup is deleted, then all the differentials are useless, so they should be deleted as well.
Keep at least one full week of intact backups. i.e. If I tell the cleanup task (on the 8th day) to delete files after 7 days and it deletes a FULL, then the remaining 6 days of differentials become invalid and now I only have 1 day of backups.


I would like to treat the transaction logs the same way, but I only want point in time recovery for 3 days. After that it will be expected to fall back to the most recent, suitable differential backup w/o transaction logs. So, I would delete transaction logs older than 3 days as well.
Does the expiration policy on the backup task "lock" those files from being deleted before they expire?


No. The Backup set will expire setting indicates when the backup set can be overwritten by another backup. It does not cause the file to be deleted, nor does it prevent the file from being deleted. 


  In essence, could I set the cleanup task to delete files older than 1 day, and yet not delete backup files that haven't yet expired?


No. The cleanup task will delete the old files. And any backup sets within those files will be gone, regardless of their expiration setting.


  If a FULL backup is deleted, then all the differentials are useless, so they should be deleted as well.


You are correct that the differential backups are then useless. But the cleanup task that deletes the full backup doesn't know that, so it has no way to delete the differentials. You just have to wait until the cleanup task runs the next time, and the next time, and the next time, and it will eventually delete the differential backups, one at a time as they reach the cutoff age (7 days, in your example).