Is it possible to add secondary replica database to the AG by restoring copy only full and log backups of the primary replica database

Question

We had issue with the AG synchronization for one of the databases that is part of an Availability group.

The plan is to remove the secondary database from the AG and restore the full backup log backups from primary replica.

Can we add the secondary replica database back to AG by taking copy only full and copy only log backups on primary server and restoring the backups to secondary?

Thanks in Advance!

Answer 1

Yes - you can restore from a copy_only backup. A copy_only backup does not affect the log chain - so you can apply transaction logs from that point forward to bring the database up to the same point in time as the primary.

Just remember - you must apply all transaction log backups taken since that copy only backup. If new log backups occur they must also be applied - to prevent this I would do the following:

1) Restore copy only backup to secondary - no recovery
2) Copy log backups to secondary and start restoring - with no recovery
3) Stop log backups on primary
4) Copy latest log backups to secondary and restore - with no recovery
5) Join database to AG
6) Start log backups on primary

This temporary halting of log backups just allows you the time to restore the final log backups and join the database to the AG. If you try to perform the join - and a new log backup has occurred it will fail, so the easiest way to get by that issue is to temporarily stop the log backups on the primary for that database.