5

Think I already know the answer to this but I'll ask anyway.

Situation is:

  • SQL 2012 Always On - 2 synchronous replicas.
  • 7 TB worth of Sharepoint DBs in 1 AG.
  • Before patching - I want a Full DB backup.
  • Backup share is on Secondary server B.
  • Backup from server A to backup share on server B = 12 hours.
  • Backup from server B to backup share on server B = 6 hours, BUT the backups need to be COPY ONLY.

If I try and restore these and create an AG - it fails. I need to take ANOTHER Full Db backup = another 6 hours, before I can recreate the AG.

So COPY ONLY backups are useless. Unless I'm missing something? What's the point of these?

8

Copy only backups are generally used for situations where you need to restore the data for an unusual reason without disrupting the current backup chain (assuming there is one). Typical reasons are restores to dev boxes to test code or look at an irregularity in the data. You get the idea.

As a side note, taking backups from AG secondaries is kind of a donkey. If the AG is behind at all, your backups are behind. If you're not already using the secondary as a readable replica, they make it an expensive behind-backup device since now you have to fully license it (assuming you have software assurance, and all that).

  • Thanks for replying. I think i'll need to failover ot server B then run a Full backup, then patch. – user188616 Sep 5 at 13:43
  • 2
    We use COPY_ONLY backups on our remote secondaries, so we have a local copy in the event we need one nearby instead of copying a full backup from NY to CO - that's an expensive and slow process. – Taryn Sep 5 at 15:03
  • Non-Copy-Only Full Backups never break the log chain. They only reset the differential base. – David Browne - Microsoft Sep 5 at 19:56
7

A database is required to have a full backup before it can be added to an availability group, and the method of creating the database has no impact on this requirement.

You have to do a full backup on a database prior to adding it to an availability group, whether you:

  • Create a new database
  • Restore it from a full backup (including any combination of differentials and logs)
  • Restore it from a copy-only backup
  • Attach it

So if you end up in a situation where you have to restore the database, you will have to delay adding it to the availability group until you've done a full backup regardless of what type of backup was restored. According to Backup to NUL, you can backup to a NUL device to satisfy this requirement. This would be the fastest possible method to get the database back into the availability group. I haven't tested it so I can't confirm it works.

  • I just configured Availability groups and indeed I did backups to nul with no problems. AG is working great. – Racer SQL Sep 6 at 12:33

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.