Introduction

SQL Server’s database-snapshot feature is a wonderfully simple tool: at the instant you create the snapshot, every page in the database is marked “copy-on-write.” Nothing is copied across the wire, no blocking locks appear, and the snapshot opens immediately as a read-only database on the local replica. Queries against the snapshot see the world exactly as it looked at that moment while the live workload keeps changing pages in the primary data files. Because snapshots live only in sparse files on the server that owns them, they are not a replacement for backups—but they are perfect for ad-hoc reporting, quick “before-and-after” comparisons, or a safety net when you want an easy way to back out a risky change that should finish within minutes or hours.

Always On Availability Groups

You can create a snapshot on the primary replica or on any readable secondary, and data movement never pauses. The extra I/O occurs only on the node that owns the snapshot, because that node now has to write changed pages into its sparse file in addition to the normal redo or active workload. The snapshot is invisible to the other replicas and it does not survive fail-over.

Reverting to the snapshot is a different story. The database must leave the AG first, the RESTORE DATABASE … FROM DATABASE_SNAPSHOT command must run on the replica that hosts the snapshot, and the database must then re-join the AG. Because the restore resets log-sequence numbers, every secondary has to perform a full automatic-seeding cycle. On a large database the reseed window can easily dwarf the few seconds it takes to issue the RESTORE itself, so plan the outage around the reseed rather than the rollback.

Log Shipping

On a log-shipping primary, a snapshot sits happily alongside the regular cycle of log backups, copy jobs, and restores. Because snapshots store old versions of data pages, they do not keep any virtual-log-file segments active, so log truncation continues exactly as before.

A log-shipping secondary, by contrast, can never host a snapshot; that database is stuck in the RESTORING or STANDBY state and snapshots require the database to be fully ONLINE. If you revert the primary to its snapshot, the log chain breaks and further log restores fail. The only remedy is to take a fresh full backup of the primary, copy it over, restore it, and let log shipping start anew.

Transactional Replication

A snapshot on the publisher is invisible to both the Log Reader Agent and the Distribution Agent. They stream transactions from the log and through the distribution database without noticing the snapshot’s existence. You can also create snapshots on subscribers, which is handy when the subscriber runs large reporting queries that you would rather not aim at the live subscription database.

Restoring the publisher to a snapshot rewinds log-sequence numbers that replication uses to track which transactions have been delivered. When that happens, the publication and every subscription are marked invalid and must be re-initialised—either with a fresh replication snapshot or by shipping new full backups to each subscriber.

Practical Perspective

The thread that runs through every scenario is simple: the act of creating a snapshot is always safe and lightweight, but restoring from it forces you to rebuild the high-availability or data-movement link. Automatic reseeding of Availability Groups, fresh full-backup initialisation of log shipping, or full re-initialisation of replication all take time that scales with database size and downstream bandwidth. Measure that time on a test system before you rely on the snapshot as a fast-rollback tool in production.

Snapshots themselves consume storage proportional to the amount of data that changes after they are created. On a busy OLTP database they can grow quickly, so keep an eye on free space and drop snapshots as soon as they have served their purpose. If you need an archived point-in-time copy that will live longer than a few hours, a full backup is still the better choice.

Used with these constraints in mind, snapshots slot comfortably into environments that already depend on Availability Groups, log shipping, or replication. They give you an immediate frozen view of your data, they never block the primary workload, and they let you answer “what did this look like an hour ago?” in seconds—provided you plan for the reseed or re-initialization work that follows a rollback.

If you would like to see a live demonstration—building a snapshot on both a primary and a secondary replica, tracking sparse-file growth, and walking through a controlled rollback—let me know and we can schedule a short session tailored to the scenarios that matter most to you.