Database snapshots provide a read-only copy of a database for use as part of a backup/recovery strategy and can also be used for reporting purposes. However, offline or corrupted databases cannot be restored via a Database Snapshots - so tape backups will still be required. Database Snapshots are only available in Enterprise editions of SQL Server 2008.

Database snapshots are incompatible with FILESTREAM filegroups: if you snapshot a database with FILESTREAM data, the FILESTREAM group will be unavailable in the snapshot and the snapshot cannot be used for reverting the source database.

Database Snapshots are created with the CREATE DATABASE command:

CREATE DATABASE db_snapshot_name
ON (	NAME = logical_file_name
	FILENAME = physical_file_name),...,
AS SNAPSHOT OF source_database_name

When defining a database snapshot, each logical file in the source database must be matched in the destination database

The following restrictions apply to the use of a Database Snapshot:

• backup, restore or detach operations are not available
• snapshot must be on the same instance as source
• full-text indexes are not available in the snapshot
• no DDL allowed on snapshot
• database snapshots prevent drop, restore and detach operations on source database
• snapshots cannot reference filegroups that are offline, defunct or being restored

Mirrored databases are inaccessible to queries, but a snapshot of the mirror can be used for queries

Storage for Snapshots

SQL Server 2008 uses 'sparse files' to implement snapshots. At creation time the snapshot does not contain data. As data changes in the source database, the before image of the extent (eight pages) is written to the snapshot. Once an extent has been copied to the snapshot, further changes to the extent do not need to be copied over because the snapshot already has its point-in-time copy.

Queries against the snapshot retrieve their data from:

1. the source database if the data has not been changed
2. the snapshot for extents that have changed in the source

Multiple snapshots can be created for a database, and extents are copied to each once the first change is seen in the extent since the snapshot creation

Restoring Data

Data in the source database can be restored using INSERT|UPDATE|DELETE|MERGE statements with appropriate filters applied. Alternatively a whole snapshot can be used to revert a database back to its state at the time the snapshot was created using the RESTORE DATABASE commmand:

RESTORE DATABASE database_name
FROM DATABASE_SNAPSHOT = snapshot_name

There are several restrictions that apply to the RESTORE DATABASE FROM DATABASE_SNAPSHOT command:

• only a single snapshot can exist for the database
• full-text catalogues on the source must be dropped before the restore
• the transaction log will be rebuilt
• both source and snapshot will be offline during the restore
• source database cannot contain FILESTREAM data