SQL Server 2012 Always-On Backup – Full vs Copy-Only

availability-groupsbackupsql serversql-server-2012

I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required.

I have SQL Server 2012 setup with always on for multiple databases. The availability groups are setup with preferred backup on replica.

only copy-only and transaction log backups are possible on replica. Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?

Thank you

Best Answer

only copy-only and transaction log backups are possible on replica

True.

Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?

Short answer YES.

From BOL :

A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

So to answer your question, a COPY_ONLY backup cannot be a part of a restore that involves T-log backups (doing a point-in-time restore). Its whole point is to have a backup set outside regular backup chain NOT impacting the restore sequence.

A full backup must be performed on primary database (cant be a copy_only backup).

Only T-log backup (as mentioned above) can be done on either primary or secondary with a CAVEAT that it wont mess up the LSNs on the PRIMARY i.e it will keep the LSNs consistent - regardless of where you take log backups in the availablity group.

Best is to check the sys.fn_hadr_backup_is_preferred_replica so the log backups will use the Availablity group backup preferences for Log backups.

Refer to : Performing Transaction Log Backups using AlwaysOn Availability Group Read-Only Secondary Replicas - Part 1

Related Question