SQL Server Database Snapshots

A SQL Server database snapshot is a read-only view of what the data pages in the source database looked like, at the time that the snapshot was created. Typically, snapshots are used to provide a point-in-time view of the database (for reporting or auditing purposes) or to allow for quick reversions during database upgrades/modifications. Since the snapshot only contains information on which values have changed, and what they were originally, it’s usually faster to revert the snapshot than having to restore the entire database from backup.

Limitations

Snapshot compatibility for SQL Server versions, editions, features, and databases:

  • SQL Server 2005-2016
    • Enterprise edition only
  • SQL Server 2016 SP1 (and newer)
    • All editions support snapshots
    • Snapshots prohibited for:
      • System databases (modelmaster, and tempdb)
      • FILESTREAM filegroups (marked as offline in the snapshot)
      • MEMORY_OPTIMIZED_DATA filegroups
    • Mirrored databases must be in the SYNCHRONIZED state.
    • Availability group databases cannot be in the RESOLVING state.
    • Full-Text Catalogs are not propagated to the snapshot database.

Snapshots are directly dependent on their source database. If the source files become corrupt or unavailable, the snapshot will not be usable.

Internals

The physical database snapshot files are sparse files. They contain a copy of the original (blank) data page, and the original values of the source data pages are copied to the snapshot page (when the source value changes). The sparse file(s) are initially 3MB (size on disk) and will grow in 64KB increments, up to the size that the source database was at the time the snapshot was created. This will only happen if every value in the source database is changed.

New (empty) snapshot files
Original values in the database are copied to the sparse files when they change.
Subsequent changes are not reflected in the snapshot
Once all original table values have changed, the snapshot files are the same size as the original database.

Permissions

User permissions to the database snapshot are static (read-only) at the time of snapshot creation. Permission changes to the database do not apply to the snapshot, and conversely, additional users cannot be granted access to the snapshot.

To create a snapshot a user must have Create Database and db_owner permissions.

Snapshot Creation

Currently, database snapshots can only be created using the CREATE DATABASE statement. The create statement requires specifying the logical name of every data (ROWS) file in the source database, and a physical file path for the associated sparse file. Log files, offline/defunct files, and restoring files are not included.

CREATE DATABASE <snapshot name> ON (
	NAME = <logical filename 1>,
	FILENAME = <physical filename 1>),
(
NAME = <logical filename 2>,
	FILENAME = <physical filename 2>)
AS SNAPSHOT OF <database name>;

Removing Snapshots

There are 2 options (DROP or REVERT) for removing database snapshots. Dropping the snapshot will delete the snapshot (and associated sparse files), leaving the source database in its current state (changes since the snapshot was created will remain). Reverting the snapshot will overwrite the original (source) database with the values contained in the snapshot.

DROP DATABASE <snapshot name>;

When the DROP command is issued, all user connections to the snapshot are terminated before the sparse files are deleted.

Reverting a snapshot is a bit of a misnomer. Reverting the source database (back to the point it was at when the snapshot was created) entails restoring the database from the snapshot.

RESTORE DATABASE <database name>
FROM DATABASE_SNAPSHOT <snapshot name>;

There are a few caveats on reverting a snapshot:

  • There can only be 1 snapshot on the database to be reverted. All other snapshots must be dropped before the restore command is executed.
  • All files that were online when the snapshot was created must be online when the restore command is run.
  • Reverting a snapshot will overwrite & rebuild the log file, which will break the log backup chain. A new Full backup will need to be taken to establish a backup chain for the restored database.
  • All full-text catalogs will be dropped.
  • Both source and snapshot databases will be unavailable during the restore operation (databases are marked as ‘In Restore’).
  • The user account running the restore command must have Restore Database or Sysadmin permissions.

Performance

Creating snapshots on a database can have performance issues for both the database and the snapshot.

  • When a query is run against a snapshot, if the value/data is not in the snapshot, then the value is obtained from the source database (resulting in 2 read operations).
  • Placing the snapshot file(s) on the same disk(s) as the source database can increase disk contention, slowing both.
  • Index operations on the source database are copied to the snapshot, resulting in slower performance.
  • New indexes on the source database are not reflected in the snapshot.

2 thoughts on “SQL Server Database Snapshots”

  1. Very useful information!
    One more limitation is size of snapshot file which is OS limitation.
    Example, Integrity check uses snapshot technology, running integrity check on very large database with high transactions results job failure due to snapshot file (sparse file) limits!

Leave a Reply

Your email address will not be published. Required fields are marked *