This feature is Enterprise only which can limit who it is useful to, but I find the whole concept fascinating. In particular the way it works.

First of all what is a Database Snapshot? A Database Snapshot is a read only copy of a live database. It remains fixed in time while changes occur to the source. The source database can be recovered back to the snapshot version fairly quickly.

Contents and size

Here is where it starts getting interesting. A snapshot initially takes up little to no space. As changes are made to the source database the snapshot grows in size. In fact the snapshot is the size of all of the pages changed in the source database since the creation of the snapshot. Basically as a page is changed in the source database a copy of the original page is made and stored in the snapshot, but only the first time. (Note: The files used to store these pages are called sparse files.) This means that if you change the same page over and over again it will only be written to the snapshot once. It then logically follows that the largest a snapshot can get is the size of the source database at the time the snapshot was taken. Since most of the time we change a very small portion of the database at any given point in time this means that snapshots tend to be much smaller than the source database. In fact you could load millions of rows into the source database (assuming they are mostly/all in new pages) and it will have little to no effect on the size of the snapshot.

Reading from a snapshot

As I said earlier a snapshot is a read only copy of the database. But if it only contains a small number of the actual pages from the source database then how does that work? When you query against the snapshot you get back the pages that are in the snapshot itself, and then any unchanged pages from the source.

What’s a snapshot for?

A snapshot is great any time you need a fixed point in time for your database. Right before a major upgrade for example. Or to create a daily reporting database. You could even create daily snapshots to protect against accidental data changes. Even though snapshots grow slowly you still want to be careful about keeping too many at once. The older a snapshot is the bigger it gets and if you fill the drive, then as usual, you are going to have issues.

Reverting to a snapshot

There are a number of restrictions on reverting back to a snapshot.

• No read-only or compressed filegroups.
• Any files that were online when the snapshot was taken still have to be online.
• The only snapshot that can exist is the one you are reverting to.

Remember that the recovery is basically just going to write back those pages that were stored in their original form. (And remove any new pages of course.) Because of this it’s usually much faster than a regular restore from a backup (less pages affected). It will however rebuild the log and break your log backup chain so make sure you take a full backup afterwards.

Quick point here. The full backup isn’t just to make sure you can recover to that point. You might think “I already took a backup before the snapshot, I’m good.” No. With the log chain broken you will need the full backup in order to restore future differential or log backups in case of an emergency.

Also important. Reverting is a one way process. Once you start it both the snapshot and the original are marked in recovery. If anything happens (an error for example) once the database is back up the recovery will continue.

Snapshots and disaster recovery

Snapshots ARE NOT FOR disaster recovery. While it is possible that the page that is corrupted actually has a copy in the snapshot it’s somewhat unlikely. And if the source database ends up in anything but an online status reverting isn’t possible anyway.

Take your backups people.

Additional Reading

Creating a snapshot
Reverting to a snapshot

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, microsoft sql server