Create, drop and revert a database snapshot
T-SQL commands to create a database snapshot, drop it, and revert a database from it, with the cautions for each step.
A database snapshot is a read-only, static view of a source database at the time it was created. It can also be used to revert the source database to that point. The commands below create a snapshot, drop it, and revert the source database from it.
Create a snapshot
CREATE DATABASE sn_AppDb ON
(NAME = AppDb, FILENAME = 'X:\sqlSnapshot\sn_AppDb_data.ss')
AS SNAPSHOT OF AppDb;
GO
Remove a snapshot
ALTER DATABASE sn_AppDb SET OFFLINE;
GO
DROP DATABASE sn_AppDb;
Revert a database from its snapshot
USE master;
RESTORE DATABASE AppDb FROM DATABASE_SNAPSHOT = 'sn_AppDb';
GO
Notes
NAMEis the logical name of a data file of the source database (checksys.database_files).FILENAMEis the path of the snapshot file.- A snapshot uses copy-on-write: the first time a page changes in the source, its original version is copied into the snapshot file. The snapshot file therefore grows with the changes, so keep free space on its drive.
- Reverting discards every change made to the source database since the snapshot was created. Back up the database first if those changes must be kept.