Iyfjky

Question

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.

Specifically:

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
deleted.

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?

hot2use 7,38841851 · Accepted Answer · 2018-08-20 14:54:42Z

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
 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 hadn't even considered digging into the script to figure out what's happening under the hood. Thanks a lot for pointing it out, taking the time to do it, and then communicating your findings so clearly. I love this community... — Aug 20 at 15:18

Scott Hodgin 15k11534 · Answer 2 · 2018-08-20 15:10:07Z

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 with minute. The only references I can find with hh 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 delete DIFF or LOG backups

Running DIFF backups never delete FULL or LOG backups

Running LOG backups never delete FULL or DIFF backups

I wish I could accept two answers as valid answers. This, in combination with @hot2use's answer is just perfect... thanks! — Aug 20 at 15:17
@Wouter - I completely understand - the answer given by hot2use is excellent and deserves the 'win'. — Aug 20 at 15:19
Well, you got my vote on the simple version. Sometimes I just think too far. — Aug 20 at 18:01

hot2use 7,38841851 · Accepted Answer · 2018-08-20 14:54:42Z

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
 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 hadn't even considered digging into the script to figure out what's happening under the hood. Thanks a lot for pointing it out, taking the time to do it, and then communicating your findings so clearly. I love this community... — Aug 20 at 15:18

Scott Hodgin 15k11534 · Answer 4 · 2018-08-20 15:10:07Z

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 with minute. The only references I can find with hh 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 delete DIFF or LOG backups

Running DIFF backups never delete FULL or LOG backups

Running LOG backups never delete FULL or DIFF backups

I wish I could accept two answers as valid answers. This, in combination with @hot2use's answer is just perfect... thanks! — Aug 20 at 15:17
@Wouter - I completely understand - the answer given by hot2use is excellent and deserves the 'win'. — Aug 20 at 15:19
Well, you got my vote on the simple version. Sometimes I just think too far. — Aug 20 at 18:01

Search This Blog

Iyfjky

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

2 Answers
2

Full Backup

Summary

Your Answer

Post as a guest

2 Answers
2

2 Answers
2

Full Backup

Summary

Full Backup

Summary

Full Backup

Summary

Full Backup

Summary

Post as a guest

Comments

Post a Comment

Popular posts from this blog

What does second last employer means? [closed]

List of Gilmore Girls characters

Confectionery

Category

Random preview

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

2 Answers 2

Full Backup

Summary

Your Answer

Sign up or log in

Post as a guest

Post as a guest

2 Answers 2

2 Answers 2

Full Backup

Summary

Full Backup

Summary

Full Backup

Summary

Full Backup

Summary

Sign up or log in

Post as a guest

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Comments

Post a Comment

Popular posts from this blog

What does second last employer means? [closed]

List of Gilmore Girls characters

Confectionery

2 Answers
2

2 Answers
2

2 Answers
2