Cdtjkyj

I have an AG with one primary replica and one secondary replica.
I have created a script that queries transaction log backups from these database replicas and restores them to a target database.

Recently one user removed one secondary database from the AG and started using it as a standalone database as explained here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/remove-a-secondary-database-from-an-availability-group-sql-server?view=sql-server-2017. The user also started a backup chain of this database.

The script now fails to restore as the recovery fork guid has changed on the secondary database.

Is there a way to detect if a database has left the AG so that in the script while I query the backupsets, I can skip the database if it is no longer part of the AG?

Thanks!

You could use exists with the sys.dm_hadr_database_replica_states DMV to see if the DB is in a AG.

Returns a row for each database that is participating in an Always On
availability group for which the local instance of SQL Server is
hosting an availability replica. This dynamic management view exposes
state information on both the primary and secondary replicas. On a
secondary replica, this view returns a row for every secondary
database on the server instance. On the primary replica, this view
returns a row for each primary database and an additional row for the
corresponding secondary database

Here is the query:

if exists(

    select 

        DBName = db_name(s.database_id)

        ,s.is_local

        ,s.synchronization_state_desc

        ,s.synchronization_health_desc

    from sys.dm_hadr_database_replica_states s

    where db_name(s.database_id) = 'your_database'

    )

begin

--do something useful

end

else

begin

--exit, probably without error

end

This would need to query against the secondary if you aren't on 2014+. If you are, then you can add s.is_primary_replica = 0 to the query. Or you can use this method or a similar approach.

You can use the following query to determine if a given database has a replica on a given server. Replace the placeholders in the WHERE clause to specify the database and server you are checking and run it on the primary.

SELECT 

  * 

FROM 

  sys.dm_hadr_availability_replica_states rs 

  JOIN sys.dm_hadr_database_replica_cluster_states dcs ON dcs.replica_id = rs.replica_id 

  JOIN sys.dm_hadr_availability_replica_cluster_states acs ON acs.replica_id = rs.replica_id 

WHERE 

  dcs.database_name = '<databasename>' 

  AND acs.replica_server_name = '<servername>'