November 7, 2017 at 2:24 pm
Hi,
I'm sure this is an easy answer for many of you but, when setting up AlwaysOn Availability Groups for the first time, I noticed the wizard takes a full backup of the primary database and restores it to the secondary server. Is this backup a copy-only backup? The reason I ask is because I have Ola's scripts running on my primary and I'm not sure if this initial backup and restore process will break my log chain.
Related, I've heard that differential backups on a secondary are useless--why is that? Should I only be taking full and t-log?
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 7, 2017 at 3:41 pm
Mike Scalise - Tuesday, November 7, 2017 2:24 PMHi,I'm sure this is an easy answer for many of you but, when setting up AlwaysOn Availability Groups for the first time, I noticed the wizard takes a full backup of the primary database and restores it to the secondary server. Is this backup a copy-only backup? The reason I ask is because I have Ola's scripts running on my primary and I'm not sure if this initial backup and restore process will break my log chain.
Related, I've heard that differential backups on a secondary are useless--why is that? Should I only be taking full and t-log?
Thanks in advance,
Mike
Last time I scripted it out on 2014, the options used were:
WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
You can always check msdb.dbo.backupset to check the properties of any backups - it includes an is_copy_only flag.
Differentials aren't supported on secondaries. Full backups Backups on the secondary only support copy_only, differentials aren't supported and copy_only isn't supported for log backups on secondaries. Supported backups for secondaries is listed in this article:
Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups)
Sue
November 7, 2017 at 4:27 pm
Mike Scalise - Tuesday, November 7, 2017 2:24 PMHi,I'm sure this is an easy answer for many of you but, when setting up AlwaysOn Availability Groups for the first time, I noticed the wizard takes a full backup of the primary database and restores it to the secondary server. Is this backup a copy-only backup? The reason I ask is because I have Ola's scripts running on my primary and I'm not sure if this initial backup and restore process will break my log chain.
Related, I've heard that differential backups on a secondary are useless--why is that? Should I only be taking full and t-log?
Thanks in advance,
Mike
Full backups do not affect the log chain in any way, regardless of the COPY_ONLY flag. The COPY_ONLY option for Full backups refers to resetting the bits that track the extents that have changed since the last non-COPY_ONLY Full backup. These bits determine what gets included in a Differential backup. If you take Differential backups, then a Full backup with COPY_ONLY would not change the base Full backup for continuing Differential backups.
If you're not using Differential backups, then setting COPY_ONLY on a Full backup would basically be no different to you than not setting COPY_ONLY for that backup.
Eddie Wuerch
MCM: SQL
November 8, 2017 at 7:37 am
Thanks, Sue and Eddie. Really great information!
Eddie, just so I understand a little better, your statement, "Full backups do not affect the log chain in any way, regardless of the COPY_ONLY flag" is only true if you're not doing differentials. If you're doing differentials, then full backups do affect the log chain, correct? It just so happens that with AOAG, differentials aren't supported, so therefore the COPY_ONLY flag becomes moot. Is my understanding correct?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 8, 2017 at 8:13 am
Mike Scalise - Wednesday, November 8, 2017 7:37 AMThanks, Sue and Eddie. Really great information!Eddie, just so I understand a little better, your statement, "Full backups do not affect the log chain in any way, regardless of the COPY_ONLY flag" is only true if you're not doing differentials. If you're doing differentials, then full backups do affect the log chain, correct? It just so happens that with AOAG, differentials aren't supported, so therefore the COPY_ONLY flag becomes moot. Is my understanding correct?
Thanks,
Mike
Full backups and differential backups do not affect the log chain.
The only thing that affects the log chain is a log backup (or a recovery model switch to simple).
COPY_ONLY on a FULL backup doesn't reset the differential bit, so if your doing FULL/DIFF/LOG backups you keep that sequence intact.
Also differential backups are supported in AOAGs as long as you do your FULL backup on the PRIMARY instance and do not offload it to a SECONDARY, as doing FULL backups on SECONDARIES requires you to do COPY_ONLY, so you will never reset the differential bit and thus get the ever increasing differential backup size.
November 8, 2017 at 8:27 am
anthony.green - Wednesday, November 8, 2017 8:13 AMMike Scalise - Wednesday, November 8, 2017 7:37 AMThanks, Sue and Eddie. Really great information!Eddie, just so I understand a little better, your statement, "Full backups do not affect the log chain in any way, regardless of the COPY_ONLY flag" is only true if you're not doing differentials. If you're doing differentials, then full backups do affect the log chain, correct? It just so happens that with AOAG, differentials aren't supported, so therefore the COPY_ONLY flag becomes moot. Is my understanding correct?
Thanks,
Mike
Full backups and differential backups do not affect the log chain.
The only thing that affects the log chain is a log backup (or a recovery model switch to simple).
COPY_ONLY on a FULL backup doesn't reset the differential bit, so if your doing FULL/DIFF/LOG backups you keep that sequence intact.
Also differential backups are supported in AOAGs as long as you do your FULL backup on the PRIMARY instance and do not offload it to a SECONDARY, as doing FULL backups on SECONDARIES requires you to do COPY_ONLY, so you will never reset the differential bit and thus get the ever increasing differential backup size.
Got it. Thanks for clearing that up for me.
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply