DB Snapshots are a very useful new feature in SQL Server 2005 Enterprise Edition. They provide us the ability to preserve a consistent picture of the database as it was at a point in time. Both metadata and data are captured in the snapshot.

 

The main uses of snapshots are for preserving views of the database for reporting purposes, and also to easily recover data affected by user error. Snapshots also provide a means to view the data in the mirror database.

 

DB Snapshots use “sparse files”, which means that the actual snapshot size on disk will be very small initially and would only grow by recording the the changes to the database using a copy-on-write scheme.

 

Another cool feature of snapshots for recovering from administrative or user error is the ability to revert a database back to a snapshot. This is potentially much quicker than restoring a full backup, as we only copy the changed pages from the snapshot back into the data files. But there are other implications of reverting to a snapshot – most important being the fact that we rebuild the log in such cases. To ensure recoverability of all transactions committed after the snapshot was taken, be sure to backup the log prior to using this feature.