sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)

Artikel
02/28/2023

Applies to: SQL Server

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

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 data type bool: 1 if the database on the current instance is on the preferred replica, otherwise 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).
      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. 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)

Dela via