<\/p>\n
<\/p>\n
SQL Server\u2019s database-snapshot feature is a wonderfully simple tool: at the instant you create the snapshot, every page in the database is marked \u201ccopy-on-write.\u201d 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\u2014but they are perfect for ad-hoc reporting, quick \u201cbefore-and-after\u201d comparisons, or a safety net when you want an easy way to back out a risky change that should finish within minutes or hours.<\/p>\n
<\/p>\n
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.<\/p>\n
Reverting to the snapshot is a different story. The database must leave the AG first, the <\/p>\n 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<\/em> pages, they do not keep any virtual-log-file segments active, so log truncation continues exactly as before.<\/p>\n A log-shipping secondary, by contrast, can never host a snapshot; that database is stuck in the <\/p>\n 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\u2019s 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.<\/p>\n 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\u2014either with a fresh replication snapshot or by shipping new full backups to each subscriber.<\/p>\n <\/p>\n The thread that runs through every scenario is simple: the act of creating<\/strong> a snapshot is always safe and lightweight, but restoring<\/strong> 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.<\/p>\n 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.<\/p>\n 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 \u201cwhat did this look like an hour ago?\u201d in seconds\u2014provided you plan for the reseed or re-initialization work that follows a rollback.<\/p>\n If you would like to see a live demonstration\u2014building a snapshot on both a primary and a secondary replica, tracking sparse-file growth, and walking through a controlled rollback\u2014let me know and we can schedule a short session tailored to the scenarios that matter most to you.<\/p>\n","protected":false},"excerpt":{"rendered":" A database snapshot in SQL Server provides an instant, read-only view of your data at a specific point in time without blocking the live workload. This post explores how snapshots interact with high-availability technologies\u2014Always On Availability Groups, log shipping, and transactional replication\u2014highlighting that creating a snapshot is always safe but restoring it severs existing sync mechanisms. You\u2019ll learn the steps required to roll back to a snapshot, including AG reseeding, log-shipping reinitialization, or replication re-initialization, and why planning for these operations is essential. The post also covers practical tips on monitoring sparse-file growth and choosing between snapshots and full backups for longer-term retention.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[36,389,149,144,13],"tags":[191,532,529,247,42,391,534,533,131,150],"class_list":["post-1016","post","type-post","status-publish","format-standard","hentry","category-availability-groups","category-log-shipping","category-replication","category-sql-auditing","category-storage-engine","tag-always-on-availability-groups","tag-copy-on-write","tag-database-snapshots","tag-disaster-recovery","tag-high-availability","tag-log-shipping","tag-rollback-strategies","tag-sparse-files","tag-sql-server","tag-transactional-replication"],"yoast_head":"\nRESTORE DATABASE \u2026 FROM DATABASE_SNAPSHOT<\/code> 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<\/code> itself, so plan the outage around the reseed rather than the rollback.<\/p>\nLog Shipping<\/h2>\n
RESTORING<\/code> or STANDBY<\/code> state and snapshots require the database to be fully ONLINE<\/code>. 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.<\/p>\nTransactional Replication<\/h2>\n
Practical Perspective<\/h2>\n