What is a database snapshot in SQL Server?
Quick Answer
A database snapshot is a read-only, point-in-time view of a database, implemented as a sparse file that copies only the original data pages as they change (copy-on-write) — cheap to create, but it lives and dies with the source database and is not a substitute for backups.
It's a read-only, frozen-in-time view of a database at the moment you created it. Internally it's a sparse file that starts basically empty; the first time a page in the source database changes, SQL Server copies the original page into the snapshot before overwriting it, so the snapshot always shows the data as it was at creation time. I've used it two ways: as a stable target for a heavy report so it doesn't contend with live writers, and as a quick rollback point before a risky migration — if it goes wrong, RESTORE ... FROM DATABASE_SNAPSHOT gets you back. The critical caveat is it's not a backup — it depends entirely on the source database's data files, so if the source is destroyed, the snapshot is worthless too.
CREATE DATABASE Sales_Snap ON (NAME=Sales, FILENAME='...') AS SNAPSHOT OF Sales;.- Uses: a stable point to run heavy reports against without blocking writers on the live database, or as a quick “undo” before a risky change via
RESTORE DATABASE ... FROM DATABASE_SNAPSHOT. - If the source database is lost, every snapshot of it is lost too — it depends on the source's data pages.
- Enterprise-edition feature; growth of the sparse file adds I/O overhead on a very write-heavy source.