The other day I read the marketing fluff on the new possibillities to backup from a secondary replica, decided to find out more about it.
After reading up on what it can and can’t do my conclusion is this:
Full backup – Only on primary replica
Full backup with copy_only – Primary and secondary replicas
Differential – Only on primary replica
Log backup – Primary and secondary replicas
So what does this mean then? Well, if diff backups are in your plan now and you don’t want to change it, then stick with full backups and diff backups on primary replica, consider offloading log backups to secondary replica. Because diff backups can not be based on a full backup with copy_only and a secondary replica can only do full backups with copy_only.
If you’re running full backups and log backups do consider running them on a secondary replica, I’ve tested restoring a full backup with copy_only leaving it with norecovery and then restoring a log backup after and it worked. But do your own testing please!
Also, if you’re backing up from the secondary replica all the time, do some restores every now and then on a testserver and run dbcc checkdb on it. Just to make sure.
Also 2, you can still backup from a secondary replica even if it is not readable. Still only full with copy_only and log backups but it’s better than nothing.
On another note, but related, today I ran a DBCC CheckDB() on a non-readable database, the secondary replica in an Availability Group, setup with synchronous mirroring, and to my coworkers surprise; it worked!
So to sum it up: On a secondary replica that is non-readable we can do:
- Full backup with copy_only
- log backup
- dbcc checkdb