Reseeding a Single Availability Group Database Without Touching the Others

Recently, I found myself dealing with an Availability Group (AG) where I needed to remove a single database from one secondary replica and then add it back. Nothing particularly unusual about that, right? Except I wanted to use automatic seeding to rebuild that database without affecting the other databases already synchronized on the replica. That led me down an interesting rabbit hole involving how SQL Server handles automatic seeding, what happens when you remove a secondary database from an AG, and how you can rebuild just one database without disrupting the others.

Let’s take a look!

The Scenario

Imagine an Availability Group, MyAG, with three replicas:

• SQL01: Primary replica
• SQL02: Secondary replica
• SQL03: Third replica

The AG contains three databases: Db1, Db2, and Db3.

All three databases were synchronized, but Db3 on SQL03 now needs to be rebuilt. Db1 and Db2 are healthy, and I don’t want to interrupt their synchronization.

Removing a Single Database from a Secondary Replica

Removing a database from one secondary is different from removing it from the entire AG.

Connect directly to SQL03 and execute:

ALTER DATABASE [Db3]
SET HADR OFF;

This removes Db3 from the AG on SQL03 only. Db3 remains in the AG on SQL01 and on any other secondaries. The local copy on SQL03 reverts to a RESTORING state, and data movement for that copy stops. Db1 and Db2 continue synchronizing without any issues.

Contrast that with this command on the primary:

ALTER AVAILABILITY GROUP [MyAG]
REMOVE DATABASE [Db3];

That removes Db3 from the Availability Group as a whole—not what we’re trying to accomplish.

Can I Just Rejoin the Database?

Before transferring the entire database again, see whether the existing secondary copy can be rejoined. If it is still in RESTORING and has a compatible recovery chain, execute on SQL03:

ALTER DATABASE [Db3]
SET HADR AVAILABILITY GROUP = [MyAG];

If the join succeeds, data movement resumes and SQL Server catches up with the primary. Depending on the backup and log history, additional log restores may be needed before the join is possible. If the secondary copy has been recovered, is incompatible, or cannot be brought forward with available logs, it’s time to initialize it again. Thankfully we can do this without affecting any of the databases that are in the availability group with DB3.

Automatic Seeding Is a Replica-Level Setting

The trick is in the knowledge that SEEDING_MODE is configured per replica, not per database.

For example, running this on the primary:

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL03'
WITH (SEEDING_MODE = AUTOMATIC);

Notice there’s no database name in that statement. I’m defining how SQL Server can initialize databases on SQL03 specifically, not telling it to reseed a specificy database.

Does changing that setting affect the other databases? No, it will not. Already-initialized, healthy databases continue moving data. There is an important caveat, though: changing to MANUAL can cancel automatic seeding operations already in progress. That’s different from stopping normal AG synchronization. So if you are trying to seed multiple databases and one of them has an issue, you might want let others succeed before attempting to fix things.

Rebuilding Db3 Using Automatic Seeding

If it is determined a fresh secondary copy is necessary, here’s the approach.

Step 1: Remove the Existing Secondary Copy

If Db3 is still joined on SQL03:

-- SQL03 only
ALTER DATABASE [Db3] SET HADR OFF;

Then, once I’ve verified I’m on the appropriate instance and have any required backups, the Db3 can be dropped from SQL03:

-- SQL03 only: permanently deletes the local copy
DROP DATABASE [Db3];

Warning: DROP DATABASE removes the local database and its files. Confirm the server, database, and primary’s health before running it. I always check: Right server, right instance, right database, right action. The 4-R’s. Now I can attempt to re-seed the database.

Step 2: Verify Automatic Seeding

On SQL01, inspect the replica’s setting:

SELECT replica_server_name, seeding_mode_desc
FROM sys.availability_replicas
WHERE replica_server_name = N'SQL03';

Step 3: Flip seeding mode of the AG:

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL03'
WITH (SEEDING_MODE = MANUAL);

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL03'
WITH (SEEDING_MODE = AUTOMATIC);

This is not a guaranteed, database-specific reseed command. Reminder: changing to MANUAL may cancel other seeding operations already underway. Other missing databases may also be eligible for automatic seeding so just be aware as to what this operation may introduce. If no attempt starts, investigate the AG configuration and seeding diagnostics rather than repeatedly toggling the setting.

Step 4: Monitor Seeding

Check the automatic-seeding attempt history:

SELECT
    start_time,
    completion_time,
    is_source,
    current_state,
    failure_state,
    failure_state_desc,
    error_code
FROM sys.dm_hadr_automatic_seeding
ORDER BY start_time DESC;

For transfer progress and recent physical seeding activity:

SELECT
    local_database_name,
    role_desc,
    internal_state_desc,
    transferred_size_bytes,
    database_size_bytes,
    start_time,
    completion_time,
    failure_message
FROM sys.dm_hadr_physical_seeding_stats;

These views help distinguish an operation that hasn’t started from one that’s transferring data or has failed. The physical-seeding DMV is not a permanent history of every attempt. Keep in mint that the timestamps are in UTC.

What About the Other Databases?

This was the question that started the exercise. Changing SEEDING_MODE does not rebuild databases that are already initialized and synchronizing normally.

The nuance is worth repeating: seeding configuration is replica-level, but initialization happens database by database. If more than one database is missing, changing the setting could initiate more work than intended.

Summary

When a database needs to be removed and rejoined or reseeded on a secondary replica, there’s no reason to disrupt the other databases that are healthy and synchronizing normally. Understanding how automatic seeding works allows you to rebuild an individual database without impacting the others. Before reseeding, though, it’s worth checking whether the database can simply be rejoined, potentially saving significant time and unnecessary data movement. Ultimately, the goal is to get the database back into a healthy, synchronized state with as little disruption as possible.