Understanding Database Snapshots in SQL Server

Database snapshots are static, read-only “pictures” of your database at a particular point in time. Available in SQL Server Enterprise Edition, they capture the database's state but do not replace backups.

Analogy 1

Imagine taking a screenshot of a text document. No matter how much you edit the document, the screenshot doesn’t change. Similarly:

You can’t modify a snapshot.
It captures your data at a specific moment.
You can restore your database using the snapshot.

SQL Syntax

CREATE DATABASE <snapshot_name>
ON ( NAME = <logical_data_filename>,
FILENAME = '<path_to_snapshot_file>' )
AS SNAPSHOT OF <database_name>;

RESTORE DATABASE <database_name>
FROM DATABASE_SNAPSHOT = '<snapshot_name>';

Key Features

Read-only, static snapshot
Captures exact state at a point in time
Stored on the same disk as original
Uses copy-on-write technology

Advantages

Fast to create
Easy rollback
Low disk usage

Disadvantages

Read-only
Depends on original DB
Not portable or a true backup

Use Cases

Testing risky changes
Performing analytics or comparisons
Quick undo before dangerous queries

Analogy 2 - Game Save Points

Think of snapshots as save points in a video game. You save your progress after level 5 and continue playing. If you fail at level 8, you can revert to the level 5 save — but the save itself never changes. If the game crashes, you lose both the game and the save point — unless you made a backup of the whole game.

🤔 Why are snapshots not backups?

Snapshots depend on the same disk as the original database.
If the disk fails, both are lost.
Backups are stored separately and offer disaster recovery.

Features of Backups

Stored on separate media
Long-term retention
Independent of live database

🚫 Can You Edit Snapshots?

No. Snapshots are read-only by design. You can query them but cannot update, delete, or insert. If you need to make changes, restore the database from the snapshot and save a new one.