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'
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
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.
Best Answer
If you are using Ola's solution, its intelligent to exclude log shipping dbs. See FAQs
.
A full backup wont break log backup chain. Only an additional NON COPY_ONLY log backup will break the log chain.
If you have folks taking adhoc log backups, you can DENY BACKUP LOG to [user|group]