MSSQL Database Snapshot

A database snapshot is more about having a frozen, query only, copy of the database than it is about backup & recovery. However, in some circumstances it may be useful to retain a 'before' image of a database when making a Change (e.g. perhaps this would be quicker to recover and/or need less storage than a typical backup/restore approach).

Create

CREATE DATABASE MyDatabase_snapshot ON

( NAME = MyDatabaseFileID,

FILENAME = 'E:\my\database\datafile\path\MyDatabaseSnapshotFileName.ss' )

AS SNAPSHOT OF MyDatabase;

GO

Note: NAME should match the FileID in your source database. FILENAME should be a new filename to store the snapshot data.
Note: If your database uses multiple data files, simply add additional bracketed name/filename stanzas, separated by commas.

Drop

DROP DATABASE MyDatabase_snapshot;

Bibliography