sys-fn-hadr-backup-is-preferred-replica-transact-sql.md

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs
sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)	sys.fn_hadr_backup_is_preferred_replica is used to determine if the current replica is the preferred backup replica.	MikeRayMSFT	mikeray	randolphwest	11/01/2024	sql	system-objects	reference	sys.fn_hadr_backup_is_preferred_replica_TSQL sys.fn_hadr_backup_is_preferred_replica fn_hadr_backup_is_preferred_replica_TSQL fn_hadr_backup_is_preferred_replica	backup on secondary replicas active secondary replicas [SQL Server], backup on secondary replicas sys.fn_hadr_backup_is_preferred_replica function	TSQL

sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)

[!INCLUDE SQL Server]

Used to determine if the current replica is the preferred backup replica.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sys.fn_hadr_backup_is_preferred_replica ( 'dbname' )

Arguments

'dbname'

The name of the database to be backed up. dbname is type sysname.

Returns

Returns data type bit: 1 if the database on the current instance is on the preferred replica, otherwise 0.

For databases that aren't part of an availability group, this function always returns 1.

If the specified database doesn't exist, the function returns a different value based on the version of SQL Server:

• Starting with SQL Server 2019 CU20, and SQL Server 2022 CU2, the function returns 0.
• In earlier versions, the function returns 1.

Remarks

Use this function in a backup script to determine if the current database is on the replica that is preferred for backups. You can run a script on every availability replica. Each of these jobs looks at the same data to determine which job should run, so only one of the scheduled jobs actually proceeds to the backup stage. Sample code could be similar to the following.

IF sys.fn_hadr_backup_is_preferred_replica(@dbname) <> 1
    BEGIN
-- If this is not the preferred replica, exit (probably without error).
        SELECT 'This is not the preferred replica, exiting with success';
    END
-- If this is the preferred replica, continue to do the backup.
/* actual backup command goes here */

Examples

A. Use sys.fn_hadr_backup_is_preferred_replica

The following example returns 1 if the current database is the preferred backup replica.

SELECT sys.fn_hadr_backup_is_preferred_replica('TestDB');
GO

Related tasks

• Configure backups on secondary replicas of an Always On availability group

Related content

• Always On Availability Groups Functions (Transact-SQL)
• What is an Always On availability group?
• CREATE AVAILABILITY GROUP (Transact-SQL)
• ALTER AVAILABILITY GROUP (Transact-SQL)
• Offload supported backups to secondary replicas of an availability group
• Always On Availability Groups Catalog Views (Transact-SQL)