What is the behavior of SQL Server Maintenance Plan - Clean Up task for deleting old backups

Question

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.

Answer 1

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).

Answer 2

The cleanup task simply runs an extended stored procedure called xp_delete_file against a list of files. This procedure checks the supplied file to validate that it is either a SQL Server backup file or a SQL Server log file. If it is, and it is older than the supplied date, the file is deleted.

For a custom solution like yours, you should use PowerShell to extract a list of backup files that meet your requirements and them remove them using Remove-Item. Something like below should work.

It queries the backup history tables in msdb then uses the returned list of backups to feed into a loop. The loop tests if the file exists and if so it removes the file.

$SQLScript = "DECLARE @DaysToKeep INT = 7,
    @LogDaysToKeep INT = 3

;WITH FullBackup AS (
    SELECT bs.database_name,
        bs.backup_start_date,
        bmf.physical_device_name,
        bs.backup_set_id,
        bs.compressed_backup_size,
        bs.first_lsn,
        'Full' AS [Type]
    FROM msdb..backupset bs WITH (NOLOCK)
    INNER JOIN msdb..backupmediafamily bmf WITH (NOLOCK) ON bmf.media_set_id = bs.media_set_id
    WHERE bs.[type] = 'D'
        AND bs.backup_start_date < DATEADD(DAY, -@DaysToKeep, GETDATE())
), DiffBackup AS (
    SELECT bs.database_name,
        bs.backup_start_date,
        bmf.physical_device_name,
        bs.backup_set_id,
        bs.compressed_backup_size,
        'Diff' AS [Type]
    FROM msdb..backupset bs WITH (NOLOCK)
    INNER JOIN msdb..backupmediafamily bmf WITH (NOLOCK) ON bmf.media_set_id = bs.media_set_id
    LEFT JOIN FullBackup fb ON fb.database_name = bs.database_name AND bs.database_name = fb.database_name AND bs.database_backup_lsn = fb.first_lsn
    WHERE bs.[type] = 'I'
        AND bs.backup_start_date >= fb.backup_start_date
), LogBackups AS (
    SELECT bs.database_name,
        bs.backup_start_date,
        bmf.physical_device_name,
        bs.backup_set_id,
        bs.compressed_backup_size,
        'Log' AS [Type]
    FROM msdb..backupset bs WITH (NOLOCK)
    INNER JOIN msdb..backupmediafamily bmf WITH (NOLOCK) ON bmf.media_set_id = bs.media_set_id
    LEFT JOIN FullBackup fb ON fb.database_name = bs.database_name AND bs.database_name = fb.database_name AND bs.database_backup_lsn = fb.first_lsn
    LEFT JOIN DiffBackup dd ON dd.database_name = bs.database_name
    WHERE bs.[type] = 'L'
        AND bs.backup_start_date >= fb.backup_start_date
        AND bs.backup_start_date <= DATEADD(DAY, -@LogDaysToKeep, GETDATE())
    ), Backups AS (
    SELECT database_name,
        physical_device_name,
        [type],
        compressed_backup_size,
        backup_start_date
    FROM FullBackup
    UNION
    SELECT database_name,
        physical_device_name,
        [type],
        compressed_backup_size,
        backup_start_date
    FROM DiffBackup
    UNION
    SELECT database_name,
        physical_device_name,
        [type],
        compressed_backup_size,
        backup_start_date
    FROM LogBackups
)

SELECT b.database_name,
    b.[type],
    b.backup_start_date,
    b.physical_device_name
FROM Backups b"

$BackupFiles = Invoke-Sqlcmd -ServerInstance localhost -Database msdb -Query $SQLScript

foreach ($File in $BackupFiles)
{
    if (Test-Path $File.physical_device_name)
    {
        Write-Output "Remove file $($File.physical_device_name)..."
        Remove-Item -Path $File.physical_device_name
    }
    else
    {
        Write-Output "File $($File.physical_device_name) no longer exists. Skipping."
    }
}

Save this as a script file on the SQL Server and call it via a SQL Server Agent job to automate the cleanup as per your requirements.