Database snapshots, which are new in SQL Server 2005, are available only in the Enterprise Edition. All recovery models previously discussed support the use of database snapshots. A database snapshot is a read-only, static view of a database captured at a specific time. It is important to be able to create multiple snapshots for use in reporting or other implementations where capturing the historical data as it sits at a specific point in time. Each snapshot is transactionally consistent with the database as of the moment of its creation.
While snapshots are useful for reporting purposes, they can also be used as a recovery mechanism. You can implement recovery by using a snapshot to revert a database to the state it was in when the snapshot was created. When working with snapshots, the database is subject to several limitations:
Although snapshots are quite useful in many instances, you should not confuse a database snapshot with the ability to use a restore operation to get the database state to a specific moment in time. The STOP_AT restore option to perform a point-in-time restore has no associated overhead, a snapshot has considerable overhead. Reverting a database by using a snapshot is the same thing as restoring from a snapshot. One of the limitations of working with snapshots is that you cannot back up or restore a snapshot. After you make a snapshot of a database, you can back up the database as you normally would; the database is unaffected by snapshots. A snapshot is subject to a number of limitations:
You can create a series of snapshots to capture database activity through time. However, each snapshot continues to operate until it is explicitly dropped. Over time, the snapshot itself continues to grow. A regular maintenance plan of deleting older snapshots is recommended.
Creating and Deleting Database Snapshots
Any user who has permission to create a database can create a snapshot, although the only way to create snapshots is by using the T-SQL CREATE DATABASE statement. The following code creates a snapshot called TWOSnapshot01 on the TWO database:
CREATE DATABASE TWO_TWOSnapshot01 ON ( NAME = TWO_Data, FILENAME = 'E:\Data\TWO_data_01.ss' ) AS SNAPSHOT OF TWO
Because creating snapshots requires SQL knowledge, the ability to create a database on the server should be strictly controlled, especially in a production environment.
To delete a snapshot, you must also use T-SQL. You use the DROP DATABASE statement with the name of the snapshot to delete a snapshot. To delete the TWOSnapshot01 snapshot you just created, for example, you would use the following command:
DROP DATABASE TWOSnapshot01
When the snapshot is dropped, any user session connected to the snapshot is terminated. Any files associated with the snapshot are deleted.
Using snapshots in SQL Server is in its infancy. Although snapshots are potentially useful, their associated overhead, limited capabilities, and numerous limitations make them rather cumbersome to use, particularly for novice administrators. These limitations, however, do no preclude the use of the material on the exam. The opposite is true; being a new feature in the product, you can expect to have exam questions centering on the appropriate use of database snapshots.
To use a database snapshot, clients and their applications need to know where to find it and how to utilize it. Users can read from one snapshot while another is being created or deleted. Users can manually connect to a database snapshot by using SQL Server Management Studio. To support snapshots in a production environment, you need a programmatic solution that transparently handles snapshots and their use in reporting and other implementations. You can schedule snapshot creation and deletion as you do most other tasks. Producing a snapshot on schedule can be useful in minimizing the administrative load associated with the feature.