Database snapshots


June 22, 2016 by Kenneth Fisher

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

4 thoughts on “Database snapshots

  1. Lee Everest says:

    I think it’s a fascinating feature too; can get really creative with it!

  2. dewitte says:

    How does it handle page splits? I can’t get my mind around how that might work. For instance, a split occurs – this means the original page goes to the snapshot. Then you update the new split page so that would go to the snapshot. Meanwhile, there is a “new” page in the main database but if you revert back, you can’t delete that new page because the stored version of the other half of the split page contains partial data. (I’m probably overthinking this)

    • Yep you are over thinking it 🙂 Try this.

      Page 1
      Row 1
      Row 2
      Row 4
      Row 5

      You insert Row 3 and there is a split.

      Page 1
      Row 1
      Row 2

      Page 2
      Row 3
      Row 4
      Row 5

      When you restore Page 2 goes away and Page 1 is pulled back from the snapshot.

      • dewitte says:

        Thanks. I re-read your section along with this and I get it. I was thinking it would write the new page one, but that would break the snapshot. Very informative. I’ve heard about snapshots but never had to use one.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: