7

When I deploy Ola Hallengren's backup script into my servers, I always set the @CleanupTime parameter for log backups to zero. This way, every time the log backup job runs, it checks for log backup files older than the last full backup and deletes them. However, this is also true for full COPY_ONLY backups! The log backup job should only check for the last non-COPY_ONLY full backup in order to delete old log backup files.

Just wondering if I'm the only that has run into this, or if what I'm suggesting makes sense. If I need to take a full copy only backup in the middle day to IDK refresh a TEST database, that copy only backup shouldn't affect the regular daily backup sequence. Let me know your thoughts, please.

Improve this question
5
  • 2
    How long between full backups? That sounds a bit risky to me. If your last full back is no good you'll have to restore to the previous full, and wont be able to roll forward. I would keep a rolling number of days.
    – Sir Swears-a-lot
    Commented Dec 20, 2017 at 2:07
  • 1
    These backups go to a network share that gets backed up every 4 hours so every day all backup files are also backed up should the need arise to go back a few days, weeks, or months. I just always thought of COPY_ONLY backups as "invisible" to the regular backup scheme but with Ola's backup script counting them as a regular FULL backup, they're not so invisible anymore.
    – Guillermo G
    Commented Dec 20, 2017 at 2:12
  • What's the benefit of deleting everything before the last full as opposed to keeping a week (if you do weekly fulls) or 24hrs (if you do daily fulls) of log backups? You still need enough space on your backup share to accommodate a full cycle of backups - why not just keep it simple?
    – Dan
    Commented Dec 20, 2017 at 2:45
  • 1
    @Dan - The old log backups are only deleted after a new full backup has completed. Keep in mind they're only deleted from the network share but they've already been backed up by the backup running on the share. Let's just say that in the share I only keep the current day's backups (full at midnight and every log backup until the next full). Not sure what is complicated about that. Also, don't mean to be an a$$ but this is not the point of my question, unless I missed something from your comment...
    – Guillermo G
    Commented Dec 20, 2017 at 2:58
  • It's not so much that your plan is overly complicated, but rather I don't see what benefit it gives you. Your share will always need to have enough free space available to hold 24hr of log backups, so what's the harm in just using a @CleanupTime param of 24?
    – Dan
    Commented Dec 20, 2017 at 3:40

3 Answers 3

Reset to default
5

Part II of II (had to split my answer)

Doing it better

Considering your RPO and RTO defined by your business, you now might want to alter the parameter of the @CleanupTime to something higher than 0. Why? Because the value 0 will only work together with the built-in fail-safe mechanism.

Let's use the following timeline:

  • 08:00 BACKUP LOG
  • 09:00 BACKUP LOG
  • 10:00 BACKUP LOG
  • ...
  • 20:00 BACKUP DATABASE
  • 21:00 BACKUP LOG
  • ...

At some point-in-time your Transaction Log backups (and Full backups?) are copied to a network drive and backed up from there by means of a backup solution, possibly combined with some kind of tape and/or disk storage. As soon as the first BACKUP LOG ... occurs after the last BACKUP DATABASE ... your previous BACKUP LOG ... files are gone...

Questions to ask yourself

  • What happens if your network backup fails?
  • When does this (tape) backup occur? Guaranteed?
  • When does the full database backup occur?
  • What do you want to be able to restore?
  • What RTO do you have?
  • What RPO does your business require?

Looking at the above questions consider using a different cleanup time (e.g. @CleanupTime=48) to keep additional hours worth of Transaction Log backups on your database server's disks.

Benefits

  • You are no longer relying on Ola's fail-safe mechanism
  • Your data is still on disk, even if you create a COPY_ONLY backup
  • Your data is still on disk, even if the network goes down and ...
    • ... you create a BACKUP DATABASE ...
    • ... a COPY_ONLY backup

Building a solid foundation

At any given point-in-time something will fail. You have to ensure that you can accommodate for any failures down the line and still guarantee to the stake-holders that your solution will be 99,.....% fool proof.

How I do it

Working with Ola's solution is an absolute breeze, IF you make one or two thoughts on how you want to recover a database and based on your businesses RPO and RTO.

My personal implementation is to have a the following schedules/retention policies:

Production systems

  • Backup TLog
    • Hourly @ xx:05 (non-SAP systems)
    • Quarter Hourly @ xx:10, xx:25, xx:40, xx:55 (SAP systems)
    • Retention : 48 hours
  • Daily Differential Backup
    • Monday - Saturday @ 20:00 (non-SAP systems)
    • Monday - Saturday @ 22:00 (SAP systems)
    • Retention : 168 hours
  • Weekly Full Backup
    • Sunday @ 20:00 (non-SAP systems)
    • Sunday @ 22:00 (SAP systems)
    • Retention : 336 hours

Test systems

The test system will be backed up during production hours. This can be 10:00 in the morning or at 14:00 in the afternoon for full and xx:15 for the Transaction Log backups.

Why I do it this way

...or my thoughts behind these decisions ...

By distributing the backup times to different slots, I am evenly distributing the disk I/O on the storage systems. No lumping of massive I/O on the disk at the full hours (FULL) or quarterly hours (TLOG).

I differentiate between SAP and non-SAP because of the sizes of the databases.

Test systems are allowed to thrash the storage during the day. No high performance impacts.

The DIFF and FULL backups occur before the tape backups start and are normally finished before the tape backups start.

The retention policies allow me to reach the RTO and RPO laid out by the business even if the (tape) backup solution is down for a day.

Backups will still work and be compliant with RTO and RPO even if the network (as a whole or only partially) is down for a day.

Your thought process

Your @CleanupTime setting was probably based on a wrong understanding of Ola's script.

Improve this answer
4
  • 1
    I feel like you should invoice me for this answer. Thank you for the detailed breakdown. You touched on all the points that slowly started to pop up in my head after running into this issue. Especially the fact that the every-four-hour backup running on the backup network share might not catch the last batch of TLOG backups before the next FULL + 1TLOG are completed. Also, didn't know you could use a FULL COPY_ONLY backup with subsequent TLOG backups to do a point-in-time restore. Definitely rethinking my backup strategy here. Great answer mate. Cheers.
    – Guillermo G
    Commented Dec 23, 2017 at 0:08
  • Nah, I'm only helping sombody out in a situation similar to one I myself was in. It takes time to understand the necessity of having sane RPO and RTO requirements which have been signed off by management. Realizing that a database is a cog-wheel in a big motor (server/application environment) further helps defining what you as a DBA can do to uphold the RTO and RPO requirements. Do your best so that nobody can hold anything against you and let managment know what you have designed (documentation).
    – John K. N.
    Commented Dec 27, 2017 at 13:10
  • This answer should be a blog post :) Most useful source of information on the topic I've found so far.
    – Wouter
    Commented Aug 20, 2018 at 12:18
  • I appreciate your positive feedback. Well it is in fact a blog post, just that i decided to post it here for the community. Enjoy.
    – John K. N.
    Commented Aug 20, 2018 at 12:30
3

Part I of II (had to split my answer)

There are one or two things to think about here. In my opinion you may have taken a shortcut in the thought process. I'll explain while I'm making this assumption...

You might want to have a quick look at my accepted answer here which was posted in reference to the question Need suggestion on Back up Strategy [closed] to give you some ideas about RTO and RPO.

Why? Because setting @CleanupTime=0 might be a bad idea...

First to answer your question.

Is it a bug in Ola's script? - No. Ola designed his script to check for the last BACKUP DATABASE... (Full Backup) and then to delete the transaction logs backups (BACKUP LOG ...) which have occurred before that backup and only if the @CleanupTime is lower than when the FULL Backup occurred.

This is a fail-safe mechanism and not meant to be used as a general feature.

Works as designed, because a BACKUP DATABASE ... WITH COPY_ONLY... is still a Full Backup.

Safety first

Creating a backup with the BACKUP DATABASE ... COPY_ONLY... option and then creating BACKUP LOG... will still permit you to restore the database to a consistent state using the FULL COPY_ONLY backup and the available Transaction Log backups.

I tested this using my StackExchange database:

  • Creating a COPY_ONLY backup manually
  • Modifying some data
  • Performing a BACKUP LOG ... using Ola's scripts
  • Restoring the database

Manual backup with COPY_ONLY

-------------------------------------------------
BACKUP DATABASE [StackExchange] TO  DISK = 'C:\adhoc\StackExchange_Full_CopyOnly_20171221_082242.bak' 
                WITH COPY_ONLY, COMPRESSION, RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'StackExchange-Full Backup Sichern', SKIP, NOREWIND, 
                     NOUNLOAD, STATS = 10, CHECKSUM
-------------------------------------------------
10 percent processed.
21 percent processed.
31 percent processed.
40 percent processed.
50 percent processed.
61 percent processed.
70 percent processed.
80 percent processed.
91 percent processed.
Processed 376 pages for database 'StackExchange', file 'StackExchange' on file 1.
100 percent processed.
Processed 2 pages for database 'StackExchange', file 'StackExchange_log' on file 1.
BACKUP DATABASE successfully processed 378 pages in 0.027 seconds (109.103 MB/sec).
-------------------------------------------------
Finished

Backup of the Transaction Log with Ola's scripts

Date        21.12.2017 08:29:06
Log     Job History (OLA DatabaseBackup - USER_DATABASES - LOG)

Step ID     1
Server      MYNOTEBOOK
Job Name        OLA DatabaseBackup - USER_DATABASES - LOG
Step Name       OLA DatabaseBackup - USER_DATABASES - LOG
Duration        00:00:01
Sql Severity    0
Sql Message ID  0
Operator Emailed    
Operator Net sent   
Operator Paged  
Retries Attempted   0

Message
Executed as user: NT Service\SQLSERVERAGENT. ...557.0  Edition: Developer Edition (64-bit)  Procedure: [msdb].[dbo].[DatabaseBackup]  Parameters: @Databases = 'USER_DATABASES', @Directory = 'C:\SQL\Backup', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 48, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @AvailabilityGroups = NULL, @Updateability = 'ALL', @LogToTable = 'Y', @Execute = 'Y'  Source: https://ola.hallengren.com     Date and time: 2017-12-21 08:29:06  Database: [AdminDB2]  Status: ONLINE  Standby: No  Updateability: READ_WRITE  User access: MULTI_USER  Is accessible: Yes  Recovery model: FULL  Encrypted: No  Differential base LSN: 36000001056400037  Last log backup LSN: 36000001061600001     Date and time: 2017-12-21 08:29:06  Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'C:\SQL\Backup\MYNOTEBOOK\AdminDB2\LOG' IF @ReturnCode  0 RAISERROR('Error creating directory.', 16, 1)  Outcome: Succeeded  Duration: 00:00:00  Date and time: 2017-12-21 08:29:06     Date and time: 2017-12-21 08:29:06  Command: 

[shortened]

  ...  Process Exit Code 0.  The step succeeded.

Restore

USE [master]
-- Restore the COPY_ONLY Backup 
RESTORE DATABASE [StackExchange] FROM  DISK = N'C:\adhoc\StackExchange_Full_CopyOnly_20171221_082242.bak' 
WITH  REPLACE, FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
-- Restore an OLA Transaction Log backup
RESTORE LOG [StackExchange] FROM  DISK = N'C:\SQL\Backup\MYNOTEBOOK\StackExchange\LOG\NB31710_StackExchange_LOG_20171221_082906.trn' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5

You may not the difference in the timestamps is approx. 7 minutes.

Results

6 percent processed.
10 percent processed.
16 percent processed.
21 percent processed.
25 percent processed.
31 percent processed.
36 percent processed.
40 percent processed.
46 percent processed.
50 percent processed.
55 percent processed.
61 percent processed.
65 percent processed.
70 percent processed.
76 percent processed.
80 percent processed.
86 percent processed.
91 percent processed.
95 percent processed.
100 percent processed.
Processed 376 pages for database 'StackExchange', file 'StackExchange' on file 1.
Processed 2 pages for database 'StackExchange', file 'StackExchange_log' on file 1.
RESTORE DATABASE successfully processed 378 pages in 0.021 seconds (140.276 MB/sec).
100 percent processed.
Processed 0 pages for database 'StackExchange', file 'StackExchange' on file 1.
Processed 2 pages for database 'StackExchange', file 'StackExchange_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (2.441 MB/sec).

Summary

Ola's script works as designed. We have verified that a BACKUP DATABASE ... WITH COPY_ONLY... and an additional BACKUP LOG ... will work together. The fail-safe feature ensures you can restore your database with the last full backup (be it COPY_ONLY or not) and the available Transaction Log backups.

(Please read Part II of II for more answers)

Improve this answer
1
1

You are right! I think this is a bug or by-design. I was able to repo the scenario.

So basically when you run Ola's script with this:

EXECUTE [master].[dbo].[DatabaseBackup] 
@Databases = 'UserShrinkSizeTest', 
@Directory = N'C:\Backup', 
@BackupType = 'FULL', 
@Verify = 'Y', 
@CleanupTime = NULL, 
@CheckSum = 'Y', 
@LogToTable = 'Y',
@CopyOnly = 'Y' --copy_only param

Or native:

-- native copy_only
BACKUP DATABASE [UserShrinkSizeTest] 
TO  DISK = N'C:\Backup\machine$SQLSERVER16\UserShrinkSizeTest\COPY_ONLY\UserShrinkSizeTest_21122017_COPYONLY.bak'
 WITH  COPY_ONLY, INIT, STATS = 1
GO

When you execute a new full backup or full copy_only backup or even differential backup, All your previous log backups will be deleted after you run another log backup (Ola's log backup script with @CleanupTime = 0 param).

Tested using Ola's script version: October 7, 2016.

Based from Ola's website:

CleanupTime

Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted.

It didn't mention about COPY_ONLY backups.

For the meantime, you can change log backup param to @CleanupTime = NULL as a workaround. Or consider moving to another backup tools (eg. Minion Backup or dbatools) or roll your own custom code because the last update of Ola's Maintenance Plan was on Oct 7, 2016. There's a github if you like to raise an issue/enhancement.

Improve this answer
2
  • 1
    Thanks for sharing your thoughts. Definitely using CleanupTime = 0 is not a good idea. I also think COPY_ONLY backup should have a distinction from non-COPY_ONLY backups (at least using Ola's scripts), otherwise why have them. I was able to modify the dbo.DatabaseBackup SP included in Ola's scripts to ignore COPY_ONLY backups when determining which TLOG backup files to delete.
    – Guillermo G
    Commented Dec 23, 2017 at 0:23
  • @GuillermoGarcia never thought of using CleanupTime = 0 param before so i thought of testing it. we learn something new everyday. and yea copy_only should be distinct in Ola script.
    – user37701
    Commented Dec 23, 2017 at 11:13

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.