Blog

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