Database Snapshots is an Enterprise feature introduced in SQL Server 2005, and represent a database and its data at a fixed point in time. Among other things, they can be useful for reporting scenarios and as a restore point (for instance after a major update).
Creating a snapshot
The database snapshot contains no physical data until the source database changes. Any select statement done on the snapshot will read the data from the source database. But when a change occurs in the source database, the snapshot will keep a record of the data as it originally was. If the changed data is read, the snapshot of the data has precedence over the source database.
So a database snapshot can be very light weight. But if there are many snapshots, and if they are kept over a long time, they can become large resource consumers. So keep the number of snapshot to a minimum, and remember to delete them as soon as possible. This T-SQL will create a demonstration database with a table and one row. It will then create a snapshot of that database:
CREATE DATABASE SnapshotDemo GO USE SnapshotDemo GO CREATE TABLE dbo.SSDemoTable ( Pk INT IDENTITY(1,1) NOT NULL ,SomeText VARCHAR(100) NOT NULL ) GO INSERT dbo.SSDemoTable (SomeText) VALUES ('Before') GO CREATE DATABASE SnapshotDemo_SS_20141025_0930 ON (NAME = SnapshotDemo, FILENAME = 'C:\MSSQL\Data\SnapshotDemo_SS_20141025_0930.ss') AS SNAPSHOT OF SnapshotDemo GO
Remember to use a descriptive name. In the example above I use [databasename]_SS_YYYYMMDD_HHMM.
The snapshot can be viewed from SSMS under Databases -> Database Snapshots:
To verify that the snapshot contains the original data only, run the following T-SQL to insert a new row and compare the results:
INSERT dbo.SSDemoTable (SomeText) VALUES ('After') GO SELECT * FROM SnapshotDemo.dbo.SSDemoTable SELECT * FROM SnapshotDemo_SS_20141025_0930.dbo.SSDemoTable
The first query to the source database contains both rows, but the query to the snapshot contains only the original row.
Deleting a snapshot
To delete a snapshot, the T-SQL syntax is the same as for any other database:
DROP DATABASE SnapshotDemo_SS_20141025_0930 GO
Restoring from a snapshot
The following syntax is used to restore or revert from a snapshot:
RESTORE DATABASE SnapshotDemo FROM DATABASE_SNAPSHOT = 'SnapshotDemo_SS_20141025_0930' GO