Log Backups in AlwaysOn

Question

Log Backups in AlwaysOn

Vijay Kumar 2,036

Hi Team,

Is it possible to take Full backup on Primary Replica and Logbackups on Secondary replica in SQL Server 2016 Alwayson?

Accepted answer

1 additional answer

Your answer

Answer 1

Cris Zhan-MSFT 6,661

Hi VijayKumar768,

Yes.
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).
https://www.sqlshack.com/sql-server-always-on-availability-group-log-backup-on-secondary-replicas/

Under the full recovery model, Full backup does not break the log sequence chain.
https://www.sqlshack.com/understanding-log-sequence-numbers-for-sql-server-transaction-log-backups-and-full-backups/

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Vijay Kumar 2,036 Reputation points

2020-10-07T16:59:41.32+00:00

Thank you Criszhan.

Here is my plan.

Weekly full backup on Primary Replica
Daily Differential backup on Primary Replica
Every 15 mins log backups on Secondary Replica

In this case how can i set backup preference?
Cris Zhan-MSFT 6,661 Reputation points

2020-10-08T01:44:46.62+00:00

Hi VijayKumar768,

You can set Prefer Secondary in Backup Preferences.

You can manually create backup jobs on the primary replica to complete full and differential backups (do not call the sys.fn_hadr_backup_is_preferred_replica function, but call the sys.fn_hadr_is_primary_replica to ensure the primary replica). And use the maintenance plan wizard to create the log backup jobs on all replicas, or create the job manually and call sys.fn_hadr_backup_is_preferred_replica function, these backup jobs use the same schedule to run.

The settings of Backup Preferences and Backup Replica Priorities only affect the return value of the sys.fn_hadr_backup_is_preferred_replica function. If you do the backup directly without calling this function, these settings will not work.
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-ver15
Cris Zhan-MSFT 6,661 Reputation points

2020-10-12T00:59:19.5+00:00

Hi @Vijay Kumar ,
Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

David Browne - msft 3,851

Yes. Just not at the same time. See Backup Types Supported on Secondary Replicas, eg

Concurrent backups, such as executing a transaction log backup on the primary replica while a full database backup is executing on the secondary replica, is currently not supported.

Shashank Singh 6,251 Reputation points

2020-10-07T05:04:55.38+00:00

anonymous usere-msft I believe you can but log backup would actually wait for full backup to complete.
Vijay Kumar 2,036 Reputation points

2020-10-08T16:18:04.987+00:00
Got it.

Even if we take all backups on Primary also same behaviour?

Share via

Log Backups in AlwaysOn

1 additional answer

Your answer