Snapshot

As explained in 100 SQL Server Mistakes and How to Avoid Them

A database snapshot is a read-only, point-in-time view of a SQL Server database that preserves pages as they existed when the snapshot was created.

A database snapshot is a read-only, point-in-time view of a SQL Server database. It uses copy-on-write technology and preserves pages as they existed when the snapshot was created. section 12.2

Use snapshots to:

• Generate reports against data from a particular point in time. section 12.2
• Compare current data with an earlier state. section 12.2
• Recover data deleted through human error or roll back a failed deployment while the source database remains healthy. section 12.2

A new snapshot starts as an empty sparse file. When a source page changes, SQL Server copies the original page into the snapshot before applying the update. Queries read preserved pages from the snapshot and unchanged pages from the source database. section 12.2

Mistake: treating a snapshot as a backup

A snapshot depends on the source database. If that database is corrupted or deleted, the snapshot is unavailable; if the instance or server fails, the snapshot cannot be moved to another instance for recovery. Keep backups as the recovery strategy, and use snapshots as a complement. section 12.2 section 12.12

Figure 12.2 Snapshot process

Use the snapshot-process figure to trace how source-page changes populate the snapshot while unchanged pages remain read from the source. section 12.2

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains copy-on-write pages, sparse files, query routing, and source dependence, but stops short of creation commands.

Examples

Uses reporting, historical comparison, accidental deletion, failed deployment, sparse files, and source-page copying as examples.

What it assumes

Assumes basic databases and pages, while introducing sparse files and copy-on-write behavior.

What you can do after

Evaluate whether a snapshot suits reporting, comparison, or quick rollback without mistaking it for an independent backup.

How it connects

Connects database snapshots with backups, storage snapshots, point-in-time restore, and deployment rollback.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

• 

  liveBookmanning.com
  Architecting an Apache Iceberg LakehouseA consistent versioned representation of a table's state, including the data files that constitute that state.
• 

  liveBookmanning.com
  PostgreSQL Mistakes and How to Avoid ThemA filesystem snapshot is a point-in-time representation of files and filesystem state that can be restored later.
• 

  liveBookmanning.com
  Amazon Web Services in Action, Third Edition: An in-depth guide to AWSA data snapshot is a point-in-time copy of stored data that can be used to restore an earlier state.
• 

  liveBookmanning.com
  API Design PatternsA snapshot is a point-in-time view of stored data that preserves the data state as of a particular instant.
• 

  liveBookmanning.com
  Elasticsearch in Action, Second EditionA snapshot is a point-in-time backup of Elasticsearch index data and cluster state that can be restored later.
• 

  liveBookmanning.com
  MongoDB 8.0 in Action, Third EditionA backup snapshot is a stored point-in-time copy of a database deployment's data for recovery.
• 

  liveBookmanning.com
  Machine Learning Platform EngineeringA data snapshot is a persisted representation of data captured at a particular point in time.
• 

  liveBookmanning.com
  ASP.NET Core in Action, 3eA tracking snapshot is a stored set of an entity's property values used to compare its original and current state.
• 

  liveBookmanning.com
  Java Persistence with Spring Data and Hibernate epubA state snapshot is a stored copy of an entity's state used to detect changes made to the managed instance.
• 

  liveBookmanning.com
  Akka in Action, Second EditionA snapshot is a persisted representation of a persistent actor's state at a point in its event history that accelerates recovery.

Compare how 11 books explain snapshot →

Related concepts

• resource revisionincludes · A resource revision is a distinct historical state of an API resource that can be retrieved or otherwise operated on separately from other states.
• provisioning toolused by · Software that creates and prepares infrastructure resources and manages their infrastructure-level dependencies.
• full backupkind of · A full backup is a complete copy of all selected data or system state at a particular point in time.
• backup and restoreused by · Backup and restore is the practice of preserving copies of data or system state and using them to recover it after loss, damage, or failure.
• snapshot isolationused by · Snapshot isolation is a transaction-isolation method in which each transaction reads a consistent versioned snapshot of committed data while concurrent writes are isolated from that view.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.