Should backups be run here?

Needed a quick way to see which databases should be backed up on a node, regardless of member of the AG or not. There is a function one can use and send the database name to it, see https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-backup-is-preferred-replica-transact-sql?view=sql-server-ver15 for how to use it.

It works but can be very slow, and I wanted to roll my own anyway, so a quick hack later I came up with the below code. It checks if we are on the node where AG backups should be done, if so prints out all the databases, even those not in the AG. If we are on the not preferred backup node then it only lists the non-AG databases. Works good enough for most circumstances.

DECLARE @SNAME VARCHAR(200)
SET @SNAME = @@SERVERNAME

--Primary and non-mirrored db should be backed up here
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'PRIMARY')
BEGIN
	SELECT dcs.database_name
	FROM sys.dm_hadr_database_replica_states AS drs
	JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id
	JOIN sys.availability_groups AS g ON g.group_id = drs.group_id
	JOIN sys.dm_hadr_database_replica_cluster_states AS dcs ON dcs.group_database_id = drs.group_database_id AND dcs.replica_id = drs.replica_id
	WHERE r.replica_server_name = @SNAME
	AND   g.automated_backup_preference_desc = 'primary'
	AND drs.database_state_desc = 'ONLINE'
	UNION
	select d.name
	from sys.databases d
	where d.database_id not in(select database_id from sys.dm_hadr_database_replica_states)
END

--Secondary and non-mirrored db should be backed up here
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'SECONDARY')
BEGIN

	SELECT dcs.database_name
	FROM sys.dm_hadr_database_replica_states AS drs
	JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id
	JOIN sys.availability_groups AS g ON g.group_id = drs.group_id
	JOIN sys.dm_hadr_database_replica_cluster_states AS dcs ON dcs.group_database_id = drs.group_database_id AND dcs.replica_id = drs.replica_id
	WHERE r.replica_server_name = @SNAME
	AND   g.automated_backup_preference_desc = 'secondary'
	AND drs.database_state_desc = 'ONLINE'
	UNION
	select d.name
	from sys.databases d
	where d.database_id not in(select database_id from sys.dm_hadr_database_replica_states)
END

Leave a Reply

Your email address will not be published. Required fields are marked *