8

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

|improve this question
2

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

|improve this answer
  • 1
    You certainly can use a COPY_ONLY full backup and apply transaction logs on top of it. A COPY_ONLY full backup is structurally the same as any other full backup. The only difference is that it does not reset the differential bitmap. After restoring the full COPY_ONLY, you can begin restoring with the transaction log that contains the last LSN of the COPY_ONLY backup, then proceed with the log chain as normal. – AMtwo Sep 20 '17 at 16:23
6

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

NO -- You can add t-log backups to a restore of a COPY_ONLY backup

|improve this answer
2

You can restore transaction logs on top of a copy-only full backup--this means that you can use a copy-only backup from the secondary replica along with transaction logs, and accomplish point-in-time recovery.

However, if you are only performing copy-only backups on the secondary replica, you won't have a "real" backup to reset the differential bitmap on the primary replica. If differential backups are part of your recovery strategy, then you need to take full backups on primary. If you every want to use a differential backup, you would need to take a full backup on the primary replica to use as the differential base.

|improve this answer
2

I have successfully tested using a copy-only backup and the log backups that span the copy-only up to the point in time desired. You do need to have all the log backups. So if you have multiple replicas that you are using to do the backups(a failover happened for instance), you need to make sure and keep track of them. In my testing I simply set things up so that all backups go to a central location. SQL maintains the log chain for log backups across all nodes in the AG. A nice article here...http://info.tricoresolutions.com/blog/understanding-backups-with-sql-server-alwayson-high-availability-mirrors

|improve this answer

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.