Database Snapshots

SQL 2005 introduces database snapshots. You create a point-in-time snapshot that coexists on the server with the original database. It actually uses the same tables until the tables change, at which point, the original data pages (only the affected pages) are copied to the snapshot file. This is implemented through the use of 'sparse files', an artifact of the NTFS. BOL gives a nice overview (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ f838de94-3ae7-49f7-9dbf-52ed30bcff81.htm if you have SQL 2005 BOL installed).

Based on some recent involvement with versioning test data and maintaining reporting data, I think this is going to be a nice feature. We can now maintain a pristine set of test data, right on the server with the database, and restore to that version after each round of testing. While testing is occurring, we can compare the original data in the snapshot with the data the tests modify. In SQL2K, this can be achieved by restoring backup files and maintaing a second database for comparisons, however, the overhead goes down considerably by having the two entities coexist.

One limitation I came up against was trying to maintain several snapshots for a single database (take a snapshot, make a change, take a snapshot, etc.). When it's time to roll back to a snapshot, only the snapshot to which you want to rollback can exist. You must first drop all the other snapshots for that database. It's nice having serval snapshot points from which to choose, however, it's frustrating to not be able to keep the other snapshot points (other than by taking backups of the source database in conjunction with the snapshots, which is how you get to do the whole snapshot thing in SQL2K, anyway).

Snapshots must be created and dropped using T-SQL. You create a snapshot with the create database statement using the AS SNAPSHOT <snapshot_name> clause. You rollback to a snapshot with the RESTORE DATABASE command using the FROM DATABASE_SNAPSHOT <snapshot_name> clause. DROP DATABASE <snapshot_name> drops the snapshot.

Snapshots can probably also be used for lightweight reporting. Take a month-end (quarter-end, year-end, etc.) snapshot and users have a read-only, point-in-time, picture of the database against which they can do their reporting (snapshot permissions can never be changed so, be sure any reporting specific permissions are in place before the snapshot is taken). I'm guessing you would still have the same old issues of your reporting database fighting for resources with your transactional database (especially since the two would actually be sharing some of the same data pages). I'd say use this technique judiciously.