3

With a SQL Server 2012 availability group, you can only take COPY_ONLY full backups on replica. You can make regular BACKUP LOG.

As it's possible to restore logs backups after a database restoration from a COPY_ONLY dump, do we really need to take full (non-COPY_ONLY) backups?

Yeah, it creates a new LSN sequence, but if we're not doing DIFFERENTIAL backups, is it something we have to think about?

By using only COPY_ONLY backups, I would hope to gain the ability to perform all backups on the read-only replica. My AG is asynchronous, so the backups might be behind the primary, but that is an acceptable risk.

I do take log backups. According to my tests, I'm able to restore any log backup on a database (restored from a COPY_ONLY or not) as soon as the log backup LSN is more recent than the full backup. The full backup changes the database_backup_lsn, the COPY_ONLY doesn't, but it seems it doesn't affect log restore. It seems I just can't restore DIF backups, but I don't need that.

There's a good explanation in the dba.se Q &A:

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

...but it does not answer my question. For now, my conclusions are: Except for the first backup (without which you will not be able to take log backups), we just can work with COPY_ONLY backups if we don't use differentials.

My (general) question is: Do we really need non-COPY_ONLY backups if we don't take differentials, and if yes, why?

Improve this question
2
  • 5
    But if you are using async then your redo may mean your secondary can be way out of sync, so your full backup at time x will not really represent the primary at time x. Please make sure you do your homework on this, for example there are many points here you may not be considering, most notably lack of history in msdb and differential backups will not be supported at all.
    – Aaron Bertrand
    Commented Oct 21, 2015 at 13:03
  • We are in a similar situation but we are using a synchronous secondary for all our backups, and doing it this way allows us to never have to take backups on our primary
    – Aaron
    Commented Jun 22, 2016 at 19:24

1 Answer 1

Reset to default
8

As long as you don't care that:

  • in the async model, the REDO queue may fall way behind, so the backups on your secondary may not represent the point in time that you think they would if they were taken on the primary.
  • you will not be able to use DIFFERENTIAL backups in this model.
  • in the event of a restore, you will have to manually determine which log backups contain the LSN ranges you'll need to restore on top of the chosen COPY_ONLY backup and, given the above, the timestamps on the bak/trn files may not be accurate enough to determine the set of files and sequence you need.
  • backup history will not be recorded centrally.

Also, I still think there are going to be scenarios where this ultra-long log chain, or relying on a COPY_ONLY backup taken in between log backups, are going to make it difficult to restore. Personally, I would be keen to perform a full, proper backup on the primary on some regular interval, say once a week. YMMV.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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