Always On and Database Backup

Database backup will not happen on the primary server unless you specific in the “Backup Preferences” of Availability Group Properties.

Note: Default is set to “Prefer Secondary”.

When setting up database backup via maintenance plan, the job will still run and report success, however the backup files will not be created because the fn_hadr_backup_is_preferred_replica(‘MyDB’) flag will be set to false.

Maintenance Plans

Note: Maintenance plan backup will check the preferred replica flag.

use [AMS]
GO
use [master]
GO
DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('MyDB'))

IF (@preferredReplica = 1)
BEGIN
    BACKUP DATABASE [MyDB] TO  DISK = N'D:\UserDBBackups\MyDB\MyDB_backup_2020_09_16_124959_1186125.bak' WITH NOFORMAT, NOINIT,  NAME = N'MyDB_backup_2020_09_16_124959_1186125', SKIP, REWIND, NOUNLOAD,  STATS = 10
END

GO

Sources:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-ver15