I've been playing around with Ola's maintenance scripts recently, and they are absolutely great. However, I was considering one scenario and was surprised when I found it wasn't catered for. Maybe I'm just missing something, so hopefully someone can help me out.
I would like to push out Ola's DB backup script to all of my instances, including those hosting AlwaysON groups. I know the script handles Primary/Secondary preferences, but if you set your backup preference to Secondary Only, then allow the script to run a normal FULL backup, the backup will not be completed because the Secondary only allows COPY_ONLY backups.
I've edited Ola's DB backup script to include a @CopyOnSecondary switch where if set to Y, the backup will automatically change the FULL backup on a Secondary to a FULL COPY_ONLY backup on the Secondary.
Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all?
Cheers.
EDIT: Listing answers
– Taking only COPY_ONLY FULL backups will not allow for DIFF backups or log truncation via log backups.
Best Answer
A COPY_ONLY full backup is still a full backup. The only difference between the two is the COPY_ONLY will not reset certain tracking bitmaps. This means your differential backups will eventually be the size of your database.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server