Availability Group - Deleting old backup files
2
votes
2
answers
522
views
I'm using Azure VMs with a SQL Server Availability Group, and Ola Hallengren' scripts. Backups are working without issues, however I've noticed that old backup files are not being removed from the server.
To remove the files, I'm running this command;
DECLARE @BackupDir NVARCHAR(4000) = (SELECT BackupDir = dbo.fn_SQLServerBackupDir())
DECLARE @Date DATETIME = DATEADD(DAY, -4, GETDATE())
EXECUTE [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;
**Note**: The function gets the default backup location for the SQL Instance.
Whenever I run this command, it completes successfully, but no files are removed. The Cleanup process that is included in the Ola Hallengren script is also unable to remove files.
In other environments, that are not AGs, the above command, plus the Cleanup in Ola Hallengren' script, work without issues.
This leads me to think the issue is either environment related or has something to do with the fact the backups where performed on databases in an AG.
Has anyone else encountered something like this before and might be able to offer some insight?
Backups are stored on the F:\ drive, which is a local disk on the server. I have also confirmed that the correct permissions have been set. At the moment, I've set full control for Everyone. (Environment is not live yet).
I tried modifying the function to add the
\
at the end of the backup path, but this did not fix the issue. I've confirmed that there are backups which meet the date requirement, too.
**EDIT 1:**
Here is the backup command that's running, which includes the @Cleanup parameter, that doesn't remove the files;
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@BackupType = 'Log',
@Verify = 'Y',
@Checksum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y',
@CleanupTime = 0,
@AvailabilityGroupDirectoryStructure = '{DatabaseName}',
@AvailabilityGroupFileName = '{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}_{Month}_{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256'
, @ServerCertificate = 'Cert_Name'
Asked by Tom.Wheater
(193 rep)
Aug 27, 2019, 12:47 PM
Last activity: Sep 22, 2022, 08:03 PM
Last activity: Sep 22, 2022, 08:03 PM