0

I have setup a distibruted availability group and trying to understand behaviour of backups on it. While I can take backup from primary replica of Primary AG, I can not take backup from primary replica from Secondary(Forwarder) AG. MS documentation says I should be able to.

In a distributed availability group, backups can be performed on secondary replicas in the same availability group as the active primary replica, or on the primary replica of any secondary availability groups. Backups cannot be performed on a secondary replica in a secondary availability group because secondary replicas only communicate with the primary replica in their own availability group. Only replicas that communicate directly with the global primary replica can perform backup operations.

Backup Command

BACKUP DATABASE [mydb] TO DISK = 'C:\TEMP\mydb_Full.BAK' WITH COMPRESSION

Error I am getting is -

Msg 3059, Level 16, State 1, Line 1
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Is this bug or I am misunderstanding something?

Backup preference is set to 'Primary' on both AG.

select sys.fn_hadr_backup_is_preferred_replica('mydb')

returns '1' on primary replicas of both Availability groups.

0

2 Answers 2

0

from the link you provided:

BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed on secondary replicas. Copy-only backups don't impact the log chain or clear the differential bitmap.

2
  • Thanks for reply. Backup preference only matters for local availability group and it should not affect backup preference of distributed AG. Though, I did try changing backup preferences on both source and target availability groups, as expected it didn't help. May 26, 2022 at 12:45
  • ops, sorry, I have updated my answer based on that clue.
    – MBuschi
    May 26, 2022 at 15:10
0

Generally you would set this on the AG Group itself. By allowing backups to happen on any replica you should then be able to.

enter image description here

2
  • Overall I suggest keeping it to primary unless you're having read/write IO performance issues.
    – Ron Zimmer
    May 26, 2022 at 15:28
  • That's my question. If sys.fn_hadr_backup_is_preferred_replica('mydb') = 1, I should be able to take normal (not COPY_ONLY) full backup on that replica. But with distributed AG setup, I am getting error on primary of Secondary AG, despite it being preferred replica for backup! May 26, 2022 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.