SQL Server : AG Backup Secondary Node

Question

On AG on every node I have maintenance plan which do backup transaction log on all user databases. The result backup is OK.

Yesterday I tried restore full backup and restore some transaction logs. All is OK. But on secondary node when the backup runs I get this alert message:

DESCRIPTION: The target database, 'activiti', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.

To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

When I check "View T-SQL" I can see only.

EXECUTE master.dbo.xp_create_subdir N'F:DBBackupactiviti'
GO
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('activiti'))

IF (@preferredReplica = 1)
BEGIN
    BACKUP LOG [activiti] TO  DISK = N'F:DBBackupactivitiactiviti_backup_2021_01_27_100424_5624364.trn' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'activiti_backup_2021_01_27_100424_5624364', SKIP, REWIND, NOUNLOAD,  STATS = 10
END
GO

I understand that maintenance plan probably does USE Activity.

But I don't know where can I set it? Or disable it and use USE Master

This problem is on MSSQL 2016 only. On MSSQL 2012 no.

question from:https://stackoverflow.com/questions/65916114/sql-server-ag-backup-secondary-node

Answer 1

Waitting for answers