add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
(No column name)

create function sys.fn_hadr_backup_is_preferred_replica (
    @database_name sysname )
returns bit
as
begin
    declare @is_preferred bit
    declare @count int
    declare @role int
    declare @ag_pref int
    declare @ar_id uniqueidentifier
    declare @ag_id uniqueidentifier
    declare @ag_resource_id nvarchar(40)
    declare @database_group_id nvarchar(40)
    declare @primary_server_name sysname
    declare @local_server_name sysname
    declare @replica_server_name sysname
    declare @islocal int

    -- If this database is in a Database Mirroring configuration, return 1 for Primary, and 0 for Secondary/mirror,
    -- since Primary is the only copy where backup will work on DBM.
    --
    select @role = mirroring_role
    from master.sys.database_mirroring
    where database_id = db_id(@database_name);

    if (@role is not null)
    begin
        if (@role = 2)
        begin
            select @is_preferred = 0
            return 0
        end
        else
        begin
            select @is_preferred = 1
            return 1
        end
    end

    -- This is not a Database Mirroring configuration.
    -- Get AG DB ID and local AG replica ID.
    --
    select
        @database_group_id = CAST(dbs.group_database_id as nvarchar(40)),
        @ar_id = replica_id
    from sys.databases dbs
    where dbs.name = @database_name

    if (@ar_id is null)
    begin
        -- Database is not in an AG, must be a standalone DB so return 1.
        --
        return 1
    end

    -- Get AG ID and local replica role.
    --
    select
        @ag_id = ars.group_id,
        @role = ars.role
    from sys.dm_hadr_availability_replica_states ars
    where ars.replica_id = @ar_id

    if (@ag_id is null or @role is null)
    begin
        -- Database is not in an AG, must be a standalone DB so return 1.
        --
        return 1
    end

    -- Get automated backup preference.
    --
    select
        @ag_resource_id = ag.resource_id,
        @ag_pref = ag.automated_backup_preference
    from sys.availability_groups ag
    where ag.group_id = @ag_id

    -- Get primary AG replica server name.
    --
    select @primary_server_name = ags.primary_replica
    from sys.dm_hadr_availability_group_states ags
    where ags.group_id = @ag_id;

    select @local_server_name = @@servername;

    declare replica_cursor cursor static local forward_only for
    select
        cast(ar.replica_id as nvarchar(36)) as replica_id,
        @ag_resource_id as resource_id,
        case ar.replica_server_name
            when @primary_server_name then 1 -- primary
            else 2 -- secondary
        end as role,
        case ar.replica_server_name
            when @local_server_name then 1
            else 0
        end as is_local
    from sys.availability_replicas ar
    where
        ar.group_id = @ag_id and
        ar.backup_priority > 0 and -- 0 is a flag that backups are never desired on this replica.
        convert
        (
            char(1),
            (
                select
                    case ar2.replica_server_name
                        when @primary_server_name then 1 -- primary
                        else 2 -- secondary
                    end
                from sys.availability_replicas ar2
                where ar2.replica_id = ar.replica_id
            )
        )
        like
            case @ag_pref
                when 0 then '1' -- Primary preferred
                when 1 then '2' -- Secondary Only
                when 2 then '2' -- Secondary Preferred
                when 3 then '%' -- No Preference
            end
    order by
        ar.backup_priority desc,
        role asc,
        ar.replica_server_name asc;

    declare @resource_id nvarchar(100);
    declare @replica_id nvarchar(100);
    declare @available bit;
    declare @dbstate int;
    declare @found int;

    set @found = 0

    open replica_cursor;

    fetch next
    from replica_cursor into @replica_id, @resource_id, @role, @islocal;

    while (@@fetch_status = 0)
    begin
        if (@role = 1) -- If it's a Primary, it has to be online unless everything is down.
        begin
            set @found = 1
            break;
        end

        select @dbstate = RetrieveDbReplicaState(@resource_id, @database_group_id, @replica_id)

        if (@dbstate = 1)
        begin
            set @found = 1
            break --We have a valid replica.
        end

        fetch next
        from replica_cursor into @replica_id, @resource_id, @role, @islocal;
    end

    close replica_cursor;
    deallocate replica_cursor;

    if (@found = 0 and @ag_pref = 2)
    begin
        -- No secondaries available, see if the primary is local.
        --
        if (@primary_server_name = @local_server_name)
        begin
            return 1;
        end

        return 0;
    end

    if (@found = 1 AND @islocal = 1)
    begin
        return 1;
    end

    return 0;
end;