SQL query to check if secondary database is removed from availability group 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.