LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon