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

title	sys.fn_hadr_backup_is_preferred_replica (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/16/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	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
dev_langs	TSQL
helpviewer_keywords	backup on secondary replicas active secondary replicas [SQL Server], backup on secondary replicas sys.fn_hadr_backup_is_preferred_replica function
ms.assetid	61b9be77-e2f6-4da1-b2ae-a62cbe226145
author	MikeRayMSFT
ms.author	mikeray

sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2012-xxxx-xxxx-xxx-md]

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

Transact-SQL Syntax Conventions

Syntax

  
sys.fn_hadr_backup_is_preferred_replica ( 'dbname' )  

Arguments

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

Returns

Returns 1 if the database on the current instance is on the preferred replica. Otherwise returns 0.

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).  
END  
-- If this is the preferred replica, continue to do the backup.  
  

Examples

A. Using 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 Backup on Availability Replicas (SQL Server)

See Also

Always On Availability Groups Functions (Transact-SQL)
Always On Availability Groups (SQL Server)
CREATE AVAILABILITY GROUP (Transact-SQL)
ALTER AVAILABILITY GROUP (Transact-SQL)
Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups) Always On Availability Groups Catalog Views (Transact-SQL)