question

Ryancerney avatar image
Ryancerney asked

Powershell Backup against AG Database Not Hitting Secondary Replica

I am using AG's in SQL 2016. I have a primary and secondary replica, Server1 and Server2 respectively. I am automating a restore process from prod to our test and dev environments via powershell. Something I noticed today is that when I check the last backup time in the database, the backup is going against the primary replica and not the secondary, even though I have the AG configured to only use secondary for backups. I am running a full backup through powershell using the Backup-SqlDatabase command. I am specifying the listener name for the server name with the backup command. What am I missing or misunderstanding? I was hoping to only hit the secondary due to resources and such, one of the benefits of AG with Enterprise right? 03' below is the primary, 07' is the secondary for reference

backupavailability-groups
ag-backup.jpg (69.0 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Shawn_Melton avatar image
Shawn_Melton answered

I do not believe that Backup-SqlDatabase supports AG backup preferences. Even if you point it at the listener name the backup process itself has to validate that configuration and the PowerShell name will not do it.

If you are wanting to backup the databases via PowerShell you will have to add some logic to check the backup preference and then adjust your connection to that secondary replica that holds that role.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Shawn. I think I'll add a check using sys.fn_hadr_is_primary_replica and then set my instance for backup based off that.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.