This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
SQL Server 2012 always-on backup, full vs copy-only
Question
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
asked 2014-04-25 by Debbie
Answer
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.
answered 2014-11-17 by Andy Mallon