Databases are a vital part of many mission-critical applications and require consistent reliable uptimes, often with a near zero Recovery Point Objective and Recovery Time Objective (RTO) of less than few minutes. One of the most critical responsibilities of a SQL Server Database Administrator (DBA) is to implement and test Disaster Recovery solution for a very low RPO and RTO using reliable disaster recovery (DR) methods.
Starting SQL Server 2022, Microsoft introduced a powerful new capability—Transact- SQL(T-SQL) Snapshot Backup—which allows you to take instantaneous, application consistent, storage level snapshots using native SQL commands. The commands work seamlessly with Google Cloud NetApp Volumes (GCNV) snapshot technology.
By combining T-SQL Snapshot Backup with the cross-regional replication of GCNV, you can achieve a powerful disaster recovery solution. This integration delivers fast recovery times and has a near-zero impact on performance, dramatically reducing your RPO and RTO. The result is enhanced reliability and greater peace of mind for your database administrators
This blog explains:
- Application consistent snapshot backups
- The simple 3-step backup workflow
- How to use replicated storage snapshots for DR
- How to validate application consistency
For detailed syntax and examples, refer to Microsoft’s official documentation.
Why Application-Consistent Snapshots Matter
Most storage snapshots only guarantee crash consistency—similar to just pulling the power cable on a computer with no regard for the state of the running applications. SQL Server 2022 T-SQL snapshot backup guarantees application consistency -- similar to a graceful shutdown, by temporarily “quiescing” SQL write activity and coordinating with storage prior to taking the snapshot.
Quiescing SQL write activity ensures:
- Writes are fully flushed
- In-flight transactions are fully written before the snapshot is taken
- Metadata is clean for recovery
- Restore behaves like a proper database backup not like a crash replay
Thus, integrating T-SQL snapshot backup solution with GCNV provides a reliable disaster recovery solution supporting point-in-time restore with application consistency.
Application‑Consistent Snapshot Backup Workflow
1. Suspend Database for Snapshot
SQL Server momentarily enters a special state called SUSPEND_FOR_SNAPSHOT_BACKUP.
This state:
- Pauses writes
- Captures necessary locks
- Ensures data and log are consistent
- Stays active only for a short duration (usually a few seconds)
2. Perform the Storage Snapshot
During suspension, on NetApp Volumes performs a point-in-time snapshot of:
- Data volume(s)
- Log volume(s)
The snapshot is instantaneous at the storage layer.
After the snapshot, SQL records the snapshot metadata into a .bkm file (a lightweight metadata-only backup).
3. Resume Database
Once the storage confirms snapshots were created, SQL is released:
- Writes resume
- Transactions continue normally
- Applications experience minimal or no downtime
That’s it—your storage now holds an application-consistent copy of your SQL database.
Using Replicated Snapshots for Disaster Recovery
Most enterprise storage arrays replicate snapshots to a secondary region or datacenter. Once the snapshot arrives at the DR site, the recovery steps are straightforward.
Following are the phases during restore at DR site -validating snapshot presence, cloning new data/log volumes, mounting them, and performing a metadata-only restore.
Step 1: Validate Snapshot Exists on DR Storage
Before recovery begins, verify the replicated snapshot is available on both:
- DR data volume
- DR log volume
Step 2: Create Cloned Volumes from Snapshot
At the DR site:
- Create new data and log volumes using the replicated snapshot as the source.
- This operation is fast because clones are created using storage metadata instead of creating full physical copies.
Step 3: Mount the Cloned Volumes
Attach the new volumes (via iSCSI or storage-specific methods):
- Do not reformat volumes—data already exists
- Assign drive letters or mount points on the DR SQL Server
Step 4: Restore the Database Using the .bkm Metadata File
The SQL Server RESTORE operation uses:
- Snapshot-backed volumes (already mounted)
- The .bkm metadata file captured during the snapshot backup
Restore uses WITH METADATA_ONLY internally to reattach files captured in the snapshot.
Step 5: Validate Application Consistency
After restore:
✔ Run DBCC CHECKDB
DBCC CHECKDB (dbname) WITH PHYSICAL_ONLY, NO_INFOMSGS;
This ensures:
- No corruption
- All pages match what SQL expects
- Log and data volumes are correctly aligned
✔ Validate LSN Continuity
Using system tables (backupset, etc.) ensures LSN chains are correct.
✔ Start the Application and Run a Smoke Test
Confirm:
- Logins succeed
- Read/write queries work
- Application UI loads normally
This completes DR validation.
