How Database Snapshots Work

A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).

Database snapshots are dependent on the source database. The snapshots of a database must be on the same server instance as the database. Furthermore, if that database becomes unavailable for any reason, all of its database snapshots also become unavailable.

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. For more information on the uses of snapshots, see Typical Uses of Database Snapshots.

Understanding how snapshots work is helpful though not essential to using them. Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file. For more information about sparse files, see Understanding Sparse File Sizes in Database Snapshots.

The following figure illustrates a copy-on-write operation. The light gray rectangles in the snapshot diagram represent potential space in a sparse file that is as-yet unallocated. On receiving the first update to a page in the source database, the Database Engine writes to the file and the operating system allocates space in the snapshot's sparse files and copies the original page there. The Database Engine then updates the page in the source database. The following figure illustrates such a copy-on-write operation.

Important

Because database snapshots are not redundant storage, they do not protect against disk errors or other types of corruption. Taking regular backups and testing your restore plan are essential to protect a database. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

Read Operations on a Database Snapshot

To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.

If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database. The following figure shows a read operation on a newly created snapshot, whose sparse file accordingly contains no pages. This read operation reads only from the source database.

After a page has been updated, a read operation on the snapshot still accesses the original page, which is now stored in a sparse file. The following figure illustrates a read operation on the snapshot that accesses a page after it has been updated in the source database. The read operation reads the original page from the sparse file of the snapshot.

Effect of the Update Pattern on Database Snapshot Growth

If your source database is fairly large and you are concerned about disk space usage, at some point you should replace an old snapshot with a new snapshot. The ideal lifespan of a snapshot depends on its growth rate and the disk space that is available to its sparse files. The disk space required by a snapshot depends on how many different pages in the source database are updated during the life of the snapshot. Therefore, if updates are mostly to a small subset of pages that are updated repeatedly, the growth rate will slow over time and the snapshot space requirements will remain relatively small. In contrast, when all of the original pages are eventually updated at least once, the snapshot will grow to the size of the source database. If the disk begins to fill up, the snapshots compete with each other for disk space. If the disk drive fills up, write operations to all the snapshots will fail.

Note

For information about learning the actual and potential sizes of a snapshot, see Understanding Sparse File Sizes in Database Snapshots.

Therefore, it is useful to know the typical update patterns for a database when planning how much space is required during the planned lifespan of a snapshot. For some databases, the rate of updates may be fairly constant; for example, an inventory database might have many of its pages updated daily, making it useful to replace old snapshots daily or weekly. For other databases, the proportion of updated pages may vary during the business cycle; for example, a catalog database might be updated primarily quarterly, with only occasional updates at other times; creating snapshots just before and after each quarterly update would be a logical strategy. The pre-update snapshot would permit reverting if a significant update error occurs, and the post-update snapshot could be used for report writing during the next quarter.

The following figure illustrates the effects of two contrasting update patterns on the size of a snapshot. Update pattern A reflects an environment in which only 30 percent of the original pages are updated during the life of the snapshot. Update pattern B reflects an environment in which 80 percent of the original pages are updated during the life of the snapshot.

Metadata About Database Snapshots

For database snapshots, database metadata includes the source_database_id property, which is stored in a column of the sys.databases catalog view. For more information about this property, see sys.databases (Transact-SQL).

Generally, a database snapshot does not expose metadata of its own, but it does expose metadata from its source database. This metadata includes, for example, the data returned by the following statement:

USE <database_snapshot> SELECT * FROM sys.database_files 

where *<database_snapshot>*is the name of a database snapshot.

The only exceptions are when the source database uses full-text search or database mirroring, which disable themselves on a snapshot by altering some values in the snapshot's metadata.

See Also

Concepts

Database Snapshots
Understanding Sparse File Sizes in Database Snapshots

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	Changed content: Clarified the note about the importance of routinely backing up your source database.
5 December 2005	New content: Explained that uncommitted transactions have been rolled back in a newly created database snapshot. Explained that database snapshots are not redundant storage, and that taking backups is essential to protect a database.