(Ola Hallengren) Deleting log backups older than last FULL backup
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:
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_ONLYbackup - Your data is still on disk, even if the network goes down and ...
- ... you create a
BACKUP DATABASE ... - ... a
COPY_ONLYbackup
- ... you create a
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.
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_ONLYbackup 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)