0

Is it possible to do the log backup from the secondary databases in the AG?

2

You can take log backup on any nodes (Primary/Secondary) irrespective of its availability mode. This is what MSDN has to say about this in Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups):

  • BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
  • Differential backups are not supported on secondary replicas.
  • BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).

    A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).
  • To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

The link below begins a good series on backups, which also dives into different scenarios like information stored in MSDB tables for backups across several nodes:

SQL 2012 AlwaysOn and Backups – Part 1 – Offloading the work to a replica by Lisa Gardner

| improve this answer | |
  • 1
    And this is a very important point about secondaries -the fact that log truncation is possible (a good thing) through backup log. Please note the important pre-requisite SYNCHRONIZED or SYNCHRONIZING states (this state depends upon whether it is synchronous or async replica) and also aim to backup all backups on all replicas to same file path. You should also investigate the use of sys.fn_hadr_backup_is_preferred_replica and backup priorities when using replicas for backup operations. – Mark Broadbent Apr 25 '16 at 15:01
  • @MarkBroadbent So does that mean if the log_reuse_wait_desc for a database on the primary replica is log backup, i can take a log backup on one of the secondary replica and the log file on the primary replica can be truncated and reused, right? – user130268 Jul 18 '18 at 8:01

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.