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
Answer by Shawn_Melton ·
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.