Sql-server – How to get more control over backup file retention using Ola Hallengren’s DatabaseBackup solution

ola-hallengrensql server

I've noticed some unexpected behavior using the database backup job in Ola's Maintenance Solution. On the test instance I'm working on, I have the user database backup job scheduled to run at 2:00am every morning. I have the @CleanupTime parameter set to 48 and I haven't specified the @CleanupMode parameter.

If I'm understanding this correctly, that should run daily and keep two copies of the the database backup… Since the default is "AFTER_BACKUP", it should delete the oldest file after the new backup completes, only leaving the oldest file if the newest backup fails. So it should start with two days' of files in the folder, create a third file (for the current day) and then delete the oldest file of the three.

What's odd is that sometimes it leaves two days' worth of files and sometimes it leaves three, even though all of the backups are successfully completing. It's not a huge issue but, in the interest of saving space on that drive, I'd prefer to only keep two backups at any given time. Any tips or suggestions would be appreciated.

Edit:
As requested in the comments below, here is the list of parameters in that job step…

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'N',
@CleanupTime = 48,
@CheckSum = 'Y',
@LogToTable = 'Y'

Best Answer

This can occur when you have variations in the runtime of your backups. If your backup job executes faster one day it may be less than 48 hours since the oldest file was created, so it will not remove it and leave you with 3 files.

Example:

  1. Backup1 created on 2019-06-17 at 21:01 - deletes backups created on or before 2019-06-15 21:01
  2. Backup2 created on 2019-06-17 at 21:03 - deletes backups created on or before 2019-06-16 21:03
  3. Backup3 created on 2019-06-17 at 20:59 - deletes backups created on or before 2019-06-17 20:59 which does not include Backup1

The easiest solution I've found for this is to subtract a few hours from the CleanupTime value. Setting to 40 or even 45 in the example above would mean Backup1 was deleted after Backup3 was taken.

Related Question