Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups
The @CleanupTime
is always specified for a specific backup job. For example, if you create a Full backup job, a Differential backup job and a Transaction Log backup job, then the @CleanupTime
always relates to the extension of the job.
Let's take a look at a Full backup example.
Full Backup
If you create a full backup job, then you will normally add one or more of the following parameters:
@Databases
: Which databases get backed up (not really relevant for this example)@Directory
: The directory to store the backups@BackupType
: Full, Differential, TLog@CleanupTime
: How much hours worth of backups to keep@FileExtensionFull
: The extension of your backup.
So you have a backup job in place that will create a full backup according to the schedule you defined for the at job. Let's assume the following:
- this job runs at 20:00 (8 p.m.)
@FileExtensionFull
has been set to'BAK'
@Directory
has been set to'F:\SQLBACKUP'
@CleanupTime
has been set to24
(hours)
If we look at the MaintenanceSolution.sql
file then you will find the description for the parameter:
SET @CleanupTime = NULL -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
Well, that's not helping much. Same as in the official documentation on the site. Let's dig further. If you scan through the script you will eventually find a section that looks like this:
The script has been wrapped to increase readability
IF @BackupSoftware IS NULL BEGIN SET @CurrentCommandType02 = 'xp_delete_file' SET @CurrentCommand02 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N''' + REPLACE(@CurrentDirectoryPath,'''','''''') + ''', -- first parameter ''' + @CurrentFileExtension + ''', --second parameter ''' + CONVERT(nvarchar(19),@CurrentCleanupDate,126) + ''' -- third parameter IF @ReturnCode 0 RAISERROR(''Error deleting files.'', 16, 1)' END
So Ola is basically using the built-in xp_delete_file function
of SQL Server to delete a file at a certain time according to:
@CurrentDirectoryPath
@CurrentFileExtension
@CurrentCleanupDate
But wait what would for example, the @CurrentCleanupDate
be? If we go back a bit in the script you can find a section that looks like this:
INSERT INTO @CurrentCleanupDates (CleanupDate, Mirror) SELECT DATEADD(hh,-(@CleanupTime),GETDATE()), 0
Ah, so the @CurrentCleanupDate
is a date addition which is calculated from the @CleanupTime
and the current time GETDATE()
. Cool.
(...and we might have just found a typo in the code, because the sections for normal and mirror database both contain Mirror
in the code.)
What is the relevant section then for @CurrentFileExtension
? Let's search around a bit again. And we find:
SELECT @CurrentFileExtension = CASE WHEN @CurrentBackupType = 'FULL' THEN @FileExtensionFull WHEN @CurrentBackupType = 'DIFF' THEN @FileExtensionDiff WHEN @CurrentBackupType = 'LOG' THEN @FileExtensionLog END
So there you have it.
Summary
If the parameters for your Full backup job are set as @FileExtensionFull='BAK'
and you have set a @CleanupTime=24
then the procedure will delete all Full backup files that are at least a day old (24 hours).
The @CurrentCommand02
that gets executed is basically:
xp_delete_file 0, 'F:\SQLBACKUP', 'BAK', '2018-08-20 20:00:00.045'
So it doesn't touch on any other backup files. (Unless of course you have defined 'BAK'
to be the extension of all backup types, in which case you lose).
I upvoted @hot2use's answer as it covers this question in detail, but I did want to share an easy way to test this stuff.
It might help you (as it helped me) to fully understand how the script works if you:
- Install the backup script (and other dependencies) on a test instance - I tested on my local computer.
- Now, alter the script and search/replace
hh
withminute
. The only references I can find withhh
are where the script is dealing with the cleanup time. This allows you to quickly run backups of various types (FULL, DIFF, LOG) see the effects of the execution because the retention is in minutes and not hours.
Run a FULL, DIFF and LOG backup of a test database and note the files created in the individual folders. This is what I used (note the CleanupTime
of 1 minute due to alter the script from hours to minutes):
exec [dbo].[DatabaseBackup]
@Databases = 'test',
@Directory = 'C:\OlaBackupTest',
@BackupType = 'full',
@Verify = 'N',
@CleanupTime = 1,
@CleanupMode = 'AFTER_BACKUP'
exec [dbo].[DatabaseBackup]
@Databases = 'test',
@Directory = 'C:\OlaBackupTest',
@BackupType = 'diff',
@Verify = 'N',
@CleanupTime = 1,
@CleanupMode = 'AFTER_BACKUP'
exec [dbo].[DatabaseBackup]
@Databases = 'test',
@Directory = 'C:\OlaBackupTest',
@BackupType = 'log',
@Verify = 'N',
@CleanupTime = 1,
@CleanupMode = 'AFTER_BACKUP'
My testing revealed the following observations:
FULL
Every execution of a FULL
backup created a new FULL
backup and deleted any FULL
backup files older than 1 minute. No DIFF
or LOG
backup files were ever affected.
DIFF
Every execution of a DIFF
backup created a new DIFF
backup and deleted any DIFF
backup files older than 1 minute. No FULL
or LOG
backup files were ever affected.
LOG
Every execution of a LOG
backup created a new LOG
backup. Continuous LOG
backups (without intervening FULL
or DIFF
backups) simply continued to accumulate in the LOG
backup folder without regard to the cleanup time. If a FULL
or DIFF
backup was eventually taken, the NEXT run of the LOG
backup deleted any LOG
backups older than the latest FULL
or DIFF
and also older than 1 minute.
No FULL
or DIFF
backup files were ever affected while running LOG
backups.
I would recommend keeping more than 1 week of FULL
backups in the event you need to go back in time to restore. Assuming 2 weeks of FULL
backups, you'd need a CleanupTime
of 336 hours.
During your testing of the 1 minute stuff, you'll see that:
- Running
FULL
backups never deleteDIFF
orLOG
backups - Running
DIFF
backups never deleteFULL
orLOG
backups - Running
LOG
backups never deleteFULL
orDIFF
backups