3

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.

Improve this question

1 Answer 1

Reset to default
4

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?

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

Improve this answer
6
  • A good point, and this would only be providing I am taking DIFF backups on the primary, yes? It would also be true then that trans logs will not be truncating the log? I guess the DBA should know this is occurring as they have set their backup preference to secondary.
    – Kingithy
    Jul 14, 2016 at 22:48
  • Correct, DIFFs must be taken on the primary. Transaction log backups, whether taken on the primary or secondary will attempt to truncate. whether or not this will happen depends on many things.
    – Sean Gallardy - Mostly Retired
    Jul 15, 2016 at 0:19
  • @SeanGallardy-Microsoft Link is broken?
    – Jaans
    Mar 31, 2018 at 11:16
  • 1
    @Jaans It still works but I've updated it with the Docs link.
    – Sean Gallardy - Mostly Retired
    Mar 31, 2018 at 15:11
  • @SeanGallardy-Microsoft Thank you. For me, I've had a ton of the msdn give me the "yellow screen of death" - would I be able to fix those by just changing the host from "msdn." to "docs." ?
    – Jaans
    Apr 1, 2018 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.