Mitch Wheat - Treat the cause, not the symptoms!

I recently had one of those “ahha!” moments with the new SQL Server 2005 Database Snapshot feature (not to be confused with the new transaction Snapshot Isolation mode). Dr Greg Low gave an overview of this great feature at the Perth .Net User Group last year, and I was going over some notes and e-learning material.

When you create a snapshot of a database, SQL Server 2005 efficiently creates a NTFS sparse file that initially contains effectively no data.

When you read data from the snapshot, SQL Server checks to see if the page the data resides upon exists in the snapshot. If it does, it serves the page from the snapshot; otherwise it serves the page from the original database.

How do pages appear in the snapshot? Each time a write is made to the original database, SQL Server checks if the page is already in the snapshot, if not it copies the page into the snapshot BEFORE the write is made to the original, thus preserving the point in time snapshot of the data. For some reason, I had wrongly assumed the page was copied after the write, but that just did not make much sense.

Database snapshots can be applied wherever you want to preserve a point in time state of a database. An excellent example of using a snapshot is point in time reporting. This is just one of many reasons why you should consider upgrading to SQL Server 2005.