Using Database Snapshots

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:

• The database cannot be dropped, detached, or restored.

• Performance is reduced due to increased I/O on the database from a copy-on-write operation to the snapshot for every page update.

• Files cannot be dropped from the database or from any snapshots.

• The source database must be online unless the database is a mirror.

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:

• A snapshot must be created and remain on the same server as the database.

• A database snapshot always works on an entire database; you cannot base a snapshot on a portion of the data.

• If a snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted.

• Snapshots are read-only.

• Snapshots of the model, master, and tempdb databases are prohibited.

• You cannot change any specifications of a snapshot file.

• You cannot drop files from a snapshot.

• You cannot back up or restore a snapshot directly, although you can revert a database to a point that a snapshot was taken by using the RESTORE command.

• You cannot attach or detach snapshots.

• You cannot create snapshots on FAT32 file system or RAW partitions.

• Full-text indexing is not supported on snapshots. Full-text catalogs are not propagated from the database.

• A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Inherited permissions cannot be changed, and permission changes made to the source are not reflected.

• A snapshot always reflects the state of filegroups at the time of snapshot creation: Online filegroups remain online, and offline filegroups remain offline.

• If a database enters the state RECOVERY_PENDING, its snapshots may become inaccessible. After the issue on the database is resolved, however, its snapshots should become available again.

• Reverting is unsupported for read-only filegroups as well as for compressed filegroups.

• In log shipping, snapshots can be created only on the primary database, not on the secondary database. If you switch roles, you must drop all the snapshots before you can set up the primary database as a secondary.

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.