SQL Server – Check if Secondary Database is Removed from Availability Group

availability-groupsbackupsql server

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!

Best Answer

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.

Related Question