Skip to content

Click to use (opens in a new tab)

What is a Database Snapshot

Introduction to Database Snapshots

A database snapshot is a read-only, point-in-time view of a database. It captures the state of the data exactly as it was at the moment the snapshot was created, while the source database continues to change. Snapshots are widely used for reporting, auditing, testing, and recovering quickly from accidental changes.

How Snapshots Work

Most implementations use copy-on-write: when a snapshot is created, no data is copied at first. As pages in the source database are modified, the original version of each page is copied into the snapshot storage. The snapshot therefore only stores the pages that have changed since it was taken, which makes creation almost instant and storage efficient.

Creating a Snapshot

Example: Creating a snapshot in SQL Server

CREATE DATABASE Sales_Snapshot_20241224
ON (NAME = Sales_Data, FILENAME = 'C:\Snapshots\Sales_20241224.ss')
AS SNAPSHOT OF Sales;

Storage systems and cloud databases (for example Amazon RDS or EBS) provide snapshot commands at the volume or instance level, which are commonly used as backups.

Snapshots vs. Backups

  • A snapshot is fast to create, depends on the source storage, and is ideal for short-term restore points (before a risky migration or bulk update).
  • A backup is a full, independent copy that can be restored even if the original storage is lost, and is the right tool for disaster recovery and long-term retention.

In practice the two are combined: take a snapshot before a change for instant rollback, and keep regular backups for durability.

Snapshot Isolation in Transactions

The term snapshot also appears in snapshot isolation, a transaction isolation level where each transaction reads a consistent view of the data as of the moment it started, implemented with multi-version concurrency control (MVCC) in databases such as PostgreSQL, MySQL/InnoDB, and SQL Server.

Working with Snapshots in Chat2DB

With Chat2DB (opens in a new tab) you can inspect data before and after a change, generate the SQL for snapshot creation, and compare result sets — making it easier to verify what a snapshot restore would bring back.