SQL Server Always-on : taking database snapshot before resume data movement.

Question

Suppose I have 3 nodes AAG , 1 ASYNC.
after failover to the ASYNC node , the former sync mode need to be resume data movement , before resume the data movement , how can I protect the former primary database by taking a backup first ? I see from the internet which we need to use database snapshot ? any other method ?

and at the moment I will need to force failed over to an async node , how can I determine the amount of data loss (by seconds , or by how much MB/GB) , given that we loss contact of the original AG primary anymore .

Answer 1

Hi @sakuraime ,

> I see from the internet which we need to use database snapshot ?

Yes. Quote from MS document

After a forced failover, all secondary databases are suspended. This includes the former primary databases, after the former primary replica comes back online and discovers that it is now a secondary replica. You must manually resume each suspended database individually on each secondary replica.

When a secondary database is resumed, it initiates data synchronization with the corresponding primary database. The secondary database rolls back any log records that were never committed on the new primary database. Therefore, if you are concerned about possible data loss on the post-failover primary databases, you should attempt to create a database snapshot on the suspended databases on one of the synchronous-commit secondary databases.

Answer 2

Hi @sakuraime ,

Create a database snapshot on synchronous-commit secondary databases in AG.

To create a database snapshot

Refer to Create a Database Snapshot (Transact-SQL)

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.