Export (0) Print
Expand All
7 out of 11 rated this helpful - Rate this topic

Database Snapshots

This topic describes the database snapshot feature, which was new in Microsoft SQL Server 2005. Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

A database snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner.

Unlike default behavior for user databases, a database snapshot is created with the ALLOW_SNAPSHOT_ISOLATION database option set ON regardless of the setting of this option on the primary database or the model system database.

Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.

Important noteImportant

Reverting does not work in an offline or corrupted database. Therefore, taking regular backups and testing your restore plan are necessary to protect a database.

NoteNote

Database snapshots are unrelated to snapshot backups, snapshot isolation of transactions, or snapshot replication.

How Database Snapshots Work

Provides a conceptual overview of the technology of database snapshots.

Typical Uses of Database Snapshots

Presents a variety of situations in which database snapshots are useful.

Limitations and Requirements of Database Snapshots

Discusses the impact of database snapshots on the source database and the system environment, as well as limitations on the snapshots themselves.

Creating a Database Snapshot

Considers some best practices for creating database snapshots and provides a link to a description of how to create them.

Understanding Sparse File Sizes in Database Snapshots

Discusses the sparse files created for database snapshots and how to view their actual and maximum sizes.

Reverting to a Database Snapshot

Discusses how reverting to a snapshot works and the restrictions on revert operations, and provides a link to a description of how to revert.

Dropping a Database Snapshot

Discusses deleting snapshots and provides a link to a description of how to delete them.

Other Resources

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.