Dealing with Multiple Database Snapshots

Last week we looked at using Database Snapshots to help with rolling back upgrades. The snapshot maintained a point in time copy of the database which could be later restored.

We can go further – a database can have multiple snapshots.

Let’s suppose we want to take one before an upgrade, another once the upgrade is complete, and another before the start of business the following day. This would provide us multiple points to restore too.

This however makes restoring more complicated.

Multiple snapshots

We previously covered how snapshots work. Changes in the source database from the point the snapshot is taken are tracked in the snapshot database. The changes in the snapshot combined with unchanged data in the source database provide a view of the database as it was when the snapshot is created.

Here’s an illustration I’ve adapted from the online documentation to demonstrate. The original (blue) data which is then modified (green). The snapshot will retain the original version. When querying the snapshot it will use a combination of data from both sources:

So why is this important?

Snapshots work by understanding what changes. However they aren’t aware of when it has changed. They only know it has changed since the snapshot was created.

Let’s suppose the following sequence of events:

• Snapshot #1 taken
• User A updates their contact details
• Snapshot #2 taken
• User B changes their password
• Snapshot #3 taken
• User A updates their details to fix a speeling mistake
• User B forgets their password and resets it

All 3 snapshots will know that users A and B have changed since they were taken. Each of them will have different states however.

Let’s consider reverting to snapshot #2.

What state would snapshot #1 be in? It would know that user B was changed (the what) but not that reverting to snapshot #2 would have restored to this previous state (the when). Snapshot #1 would be out of sync with the source database.

How about snapshot #3? That commenced from a point in time after snapshot #2. It contains the new password for user B which hasn’t yet been changed. It’s like Back to the Future, with data. That’s not a good thing in this case.

This is the issue that arises when handling multiple snapshots for a single database – consistency. So how do we deal with restoration?

Restoring a snapshot

For the reason of consistency above, we can’t simply restore a snapshot on a database which has multiple. If we try to restore a snapshot in this situation we’ll get an exception:

Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

We have to remove all other snapshots from the database before it can be restored. Even taking multiple snapshots for safety, you’re only able to revert to one of them.

There is a strategy to carefully defining points when you’d want a snapshot to revert to. Part of that however needs to include the restoration of that data as everything in the database will be restored to that one point in time.

This is also why a snapshot is not a substitute of a backup.

So let’s say we really want to revert to snapshot #2. It would look a little something like this:

DROP DATABASE [blog_snapshot1];
DROP DATABASE [blog_snapshot3];
GO

RESTORE DATABASE blog
FROM DATABASE_SNAPSHOT = 'blog_snapshot2';

It’s not terribly complex at all, is it.

Wrap up

In this post we’ve looked at how multiple snapshots for a single database are handled. They operate as individual databases for the most part. When we come to restoring them however, they’re aware of each other and the fact that only one can be restored for consistency.

It’s been a long winded way of saying that you can take multiple snapshots, but make sure you only plan to restore back to one point in time as you’ll need to lose the others.

But that would have been a very short post without the context and example.

Although multiple snapshots may have this draw back when it comes to restoration, they can be useful when wanting to see how data changes over time. We’ll look into that a bit more next week.