Summary: Learn how to create and restore SQL Server Database Snapshots. This guide provides the T-SQL syntax for static, read-only snapshots and explains how to revert a database to a specific point in time.
Working with SQL Server Database Snapshots
Starting with SQL Server 2016 SP1, the Database Snapshot feature is supported across all editions, including Standard and Express.
A database snapshot provides a static, read-only view of a database at a specific point in time. It uses a "copy-on-write" mechanism: it doesn't store a full copy of the data initially, but as pages in the source database are modified, the original versions of those pages are copied into the snapshot's sparse file.
Creating a Database Snapshot
The snapshot must reside on the same server instance as the source database. The .ss extension is a common convention, but any extension is technically valid.
-- Generic Syntax
CREATE DATABASE <database_snapshot_name>
ON (
NAME = <logical_file_name>,
FILENAME = '<os_file_name>'
) [ ,...n ]
AS SNAPSHOT OF <source_database_name>;
-- Example Implementation
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO
Restoring a Database from a Snapshot
You can quickly revert your source database to the state it was in when the snapshot was taken. This is much faster than a traditional restore but requires that no other snapshots exist on the same database.
-- Reverting the database
RESTORE DATABASE <database_name>
FROM DATABASE_SNAPSHOT = <database_snapshot_name>;
GO
Critical Considerations
- Read-Only: Snapshots cannot be modified. They are ideal for reporting or "pre-deployment" safety nets.
- Sparse Files: The snapshot file starts small and grows as the source database changes. Monitor disk space closely!
- Dependency: If the source database goes offline or the data files are corrupted, the snapshot becomes useless. It is not a replacement for a full backup strategy.
Further Reading:
- Snapshots vs. Backups: Which should you use?
- Official Microsoft Documentation: CREATE DATABASE SNAPSHOT
Pro Tip: Snapshots are perfect for protecting against "Fat Finger" errors during major data updates. Take a snapshot, run your scripts, and if something goes wrong, you can revert in seconds!
