Deleting Encrypted Backup Files

[NickColebourn]

So this isn't an issue that is likely fixable, but one that probably needs listing on the FAQs. If you encrypt your backup files to disk then xp_delete_file can't read the headers and therefore doesn't delete the files. However it "fails" silently, which means there's no obvious issue until the disk space alerts / runs out.

A possible workaround would be an additional SQL Agent job that runs a PowerShell script (working on that in my environment now).

SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Version of the script --// Version: 2022-01-02 13:58:13

[NickColebourn]

This is my PowerShell workaround based on the Ola folder structures. Tested and working as a SQL Agent Job using a PowerShell type.

$FullBackupRetention = -672 $DiffBackupRetention = -336 $LogBackupRetention = -168

$folders = Get-ChildItem -Path B:\SQLBackups -Recurse -Directory -Filter "FULL"## | Select FullName%{$_.FullName}

foreach ($folder in $folders) {

$folder.FullName

Get-ChildItem -Path $folder.FullName -File -Filter "*.bak" | Where-Object{$_.LastWriteTime -lt (Get-Date).AddHours($FullBackupRetention)}  | Remove-Item -Verbose

}

$folders = Get-ChildItem -Path B:\SQLBackups -Recurse -Directory -Filter "DIFF"## | Select FullName%{$_.FullName}

foreach ($folder in $folders) {

$folder.FullName

Get-ChildItem -Path $folder.FullName -File -Filter "*.bak" | Where-Object{$_.LastWriteTime -lt (Get-Date).AddHours($DiffBackupRetention)}  | Remove-Item -Verbose

}

$folders = Get-ChildItem -Path B:\SQLBackups -Recurse -Directory -Filter "LOG"## | Select FullName%{$_.FullName}

foreach ($folder in $folders) {

$folder.FullName

Get-ChildItem -Path $folder.FullName -File -Filter "*.trn" | Where-Object{$_.LastWriteTime -lt (Get-Date).AddHours($LogBackupRetention)}  | Remove-Item -Verbose

}