1

We have a database server where the test database is "refreshed" with the backup of prod database every day at 22:00.

  • The first backup after the restore is a differential backup, because we are using the ChangeBackupType = Y switch.

  • Our CleanupTime parameter is set to 48 hours, which works fine for Full and TLog backups.

  • However the CleanupTime parameter does not work for the Diff backups that are created because of the ChangeBackupType being set to Y.

Any ideas where the problem lies? We do not explicitly use the Diff backups job, so there is no CleanupTime I can set.

Definition Full Backup sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d aspMaintenance -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'ALL_DATABASES', @BackupType = 'FULL', @NumberOfFiles = 1, @Verify = 'N', @CleanupTime = 42, @CheckSum = 'Y', @DirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @LogToTable = 'Y'" -b

Definition Log Backup sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d aspMaintenance -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'ALL_DATABASES', @ChangeBackupType = 'Y', @BackupType = 'LOG', @Verify = 'N', @CleanupTime = 48, @CheckSum = 'Y', @DirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}_{Partial}_{CopyOnly}', @LogToTable = 'Y'" -b

1
  • Welcome to the DBA.SE community. Could you possibly edit your question and provide the job step details of your two (or three) backups jobs? I have an idea, but would like to see your SQL Server Agent Job details for the OLA backup jobs first. Thanks.
    – John K. N.
    Dec 28, 2023 at 13:23

1 Answer 1

0

Ola Hallengren's SQL Server Maintenance Solution, which you are using to perform your backup jobs, currently does no cleanup when when @ChangeBackupType = 'Y' and the backup type is changed.

I could find no official documentation of that, but the current version of the DatabaseBackup.sql script has checks that skips cleanup completely in this case, as can be seen in these two permalinks to the relevant lines of code in the official repository:

There are open issues in the official repository for enhancing cleanup, such as the two ones linked below, but there has been no progress on them for a long time.

1
  • Thanks, Marcelo. It is only one database and the restore time is always 22.00. I will create a job running the DIFF backup for this database at 22.10. Backup type has not to be changed and cleanup should work.
    – Guenter
    Jan 4 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.