I am having trouble understanding what exactly to expect from the `CleanupTime` option in the [Ola Hallengren Server Maintenance Solution]. I'm finding some related questions, and elaborate answers, but the explanations still puzzle me a bit.
I am doing a weekly FULL backup, a daily DIFF backup, and an hourly LOG backup.
The FULL backup is using the default `CleanupTime` of 24h. The DIFF and LOG backup have NULL as `CleanupTime`.
From the [documentation of the CleanupTime paramter], I fail to understand if setting the `CleanupTime` setting for a backup of `BackupType` FULL, will also delete older DIFF and LOG backup files, or *only* FULL backup files.
> Specify the time, in hours, after which the backup files are deleted.
> If no time is specified, then no backup files are deleted.
The latter paragraph makes me think that setting `CleanupTime` on backups of `BackupType` FULL will also delete older transaction logs. Yet it is unclear if this paragraph only applies to backups of the `BackupType` LOG, or also to backups of the `BackupType` FULL.
> DatabaseBackup has a check to verify that transaction log backups that
> are newer than the most recent full or differential backup are not
What I am trying to achieve, is that I can do a point-in-time recovery up to 1 week. (We have a very slowly changing database, so this is feasible) The way I understand it now, this would require a week old full backup, and a weeks worth of Transaction log backup. Since the full, and differential backups can only be used to restore to one specific point in time.
So, should I just set the `CleanupTime` option of my FULL backup job to `24*7`? What I'm guessing now is that setting it to 24h, will cause the next FULL backup to delete all older Full, diff **and** Transaction log backup files, leaving me with a point-in-time recovery window of ... 0 hours. Right?
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 to `24` (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
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)'
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:
- `@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.
<sub>_(...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.)_</sub>
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
So there you have it.
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).