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?
Best Answer
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).