基于早期及12小时前备份创建的SQL镜像同步问题及确认方法

1. Can a SQL mirror created from an earlier backup catch up to the principal database? #

Absolutely—this is core to how SQL Server mirroring is designed to work. When you set up a mirror using a backup taken at an earlier time, the mirror will automatically sync with the principal as long as two critical conditions hold:

• The transaction log chain remains intact from the backup time forward (you haven’t broken the chain with actions like a transaction log backup using WITH NORECOVERY that truncates logs prematurely).
• The mirror instance maintains a stable network connection to the principal.

Once connected, the mirror pulls all transaction log records generated after the backup was created, applies them in sequential order, and will eventually reach the exact state of the principal. The time this takes depends on how much log data needs to process and your network speed, but it will catch up given a healthy environment.

2. Will a mirror created from a 12-hour-old backup catch up, and how do I confirm synchronization is complete? #

Yes, it will catch up—again, assuming the transaction log chain is unbroken and the mirror can communicate with the principal without interruptions. Here are the most reliable ways to verify it’s fully synced:

Using SQL Server Management Studio (SSMS)

• Right-click the principal database in Object Explorer → Tasks → Mirror → Check the Mirroring Status section.
• For high-safety (synchronous) mode: Look for Synchronized in the Synchronization State field—this means the mirror is fully caught up.
• For high-performance (asynchronous) mode: The state will show Synchronizing, but you can confirm catch-up by checking that there’s no backlog in the log send queue displayed here.

Using T-SQL Queries

Run this query on the principal server to get detailed mirroring status:

SELECT 
    name AS database_name,
    mirroring_state_desc,
    mirroring_send_queue_size,
    mirroring_recovery_queue_size
FROM sys.database_mirroring dm
JOIN sys.databases d ON dm.database_id = d.database_id
WHERE mirroring_guid IS NOT NULL;

• mirroring_state_desc = 'SYNCHRONIZED': Mirror is fully aligned with the principal (high-safety mode).
• mirroring_send_queue_size = 0: No pending log records left to send to the mirror.
• mirroring_recovery_queue_size = 0: The mirror has applied all received log records.

For asynchronous mode, even if the state is Synchronizing, if both queue sizes hit 0, the mirror is effectively caught up to the principal’s current state.

内容的提问来源于stack exchange，提问作者Bill Greer