Microsoft introduced database snapshots with SQL Server 2005. A database snapshot is a static, read-only copy of a source database that gives you a consistent view of a database as of a certain point in time. This feature is available only in the Enterprise Edition of Microsoft SQL Server 2005 and all recovery models support database snapshots.

How Database Snapshots Work

When a database snapshot is created, SQL Server doesn’t physically create a separate database file. Instead, it creates a special file called a sparse file. This file is initially empty and allocated minimal disk space. As pages are modified in the source database, the original (unmodified) version of the page file is written to the sparse file. This process, known as copy-on-write, allows SQL Server to create a record of the database as it existed at the moment of creation in the sparse file.

During read operations on the snapshot, the original data is read from the source database. For the data that has been changed, the snapshot reads the information from the sparse file. This allows you to use a database snapshot in nearly any situation where you could use the database file, except when you require write access.

Using Database Snapshots

There are several typical uses for database snapshots:

• Querying a snapshot allows you to avoid blocking due to update/insert operations on the source database.
• Database snapshots are useful as a data source for reporting applications.
• They can be used as a data source for data export operations.
• Database snapshots can serve as a historic data source.
• They can be used as a redundant file copy for data redundancy or archive.
• Database snapshots can be used as a file copy for test and validations.

Managing Database Snapshots

When creating database snapshots, it is important to use names that clearly identify the source database, the file as a snapshot, and the date and time when the snapshot was created. You can create database snapshots using the CREATE DATABASE command with the AS SNAPSHOT OF clause. You must specify a snapshot file for each database file, except for the transaction log file, any offline files, or files in the process of being restored.

Database snapshots can be viewed and dropped using SQL Server Management Studio. Dropping a snapshot does not affect the source database. Reverting to a snapshot is the same as restoring the database to the point in time when the snapshot was created.

Conclusion

Database snapshots are a valuable tool for DBAs, providing a consistent view of a database at a specific point in time. While it is unfortunate that this feature is only available in the Enterprise Edition of SQL Server, it can be a useful tool for system upgrades and other scenarios where a read-only copy of a database is needed. With further development, database snapshots have the potential to become a widely-used tool in the SQL Server community.