Sql-server – (Ola Hallengren) Deleting log backups older than last FULL backup

backupmaintenanceola-hallengrensql serversql-server-2012

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.

Best Answer

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.

Related Solutions

Sql-server – How to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Sql-server – First log backup after FULL taking lots of space

Taking a FULL backup won't clear out the transaction log entries (unless the database is in SIMPLE Recovery Model). So whatever changes take place between your last LOG backup before the FULL backup (@ 4:30 AM), and the first LOG backup once that process is over (@ 6:30 AM) would be included in that 6:30 AM LOG backup. That is a 2 hour span (you typically do a LOG backup every 30 minutes otherwise) in which you, at the very least, run index maintenance via the IndexOptimize stored procedure.

If you want to test this out, just temporarily disable both the "IndexOptimize" and "Database Integrity Check" steps (meaning, if you are using the CmdExec step type so it can call SQLCMD, just add REM to the beginning of the line in both steps). Let that run for one night and if I am right, it will be very obvious. Then, remove the REM from one of the two steps so that only one of them runs again (maybe start with the "Database Integrity Check" step) and let that run for one night and then you should see how much impact each of those first two steps has on that "first" LOG backup.

If the "Database Integrity Check" step is taking up more than a small portion of that LOG backup, then you might be able to split that initial backup into two (even if not an even 50/50) by doing a LOG backup after the "IndexOptimize" step. If all (or most) of the LOG backup size is from the "IndexOptimize" step, then maybe try to schedule a LOG backup for 4:36 AM to attempt to cut the resulting LOG backup in approximately half (given that you show that first step running for just under 14 minutes).