question

sakuraime avatar image
0 Votes"
sakuraime asked Cathyji-msft answered

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

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 .

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

any one has an idea on how to backup a database before data movement?

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered sakuraime commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

but any steps on how to do that ???

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.