“Snapshots” is one of those words that gets tossed around a lot.  In a nutshell, a snapshot is a point-in-time copy of a piece of data.  It might be a single database, it might be a drive, or it might be a SAN array.

There’s (at least) 3 kinds of snapshots:

• Application-level (in this case, SQL Server, but I like to illustrate snapshots by talking about Word’s change-tracking features.  Those aren’t exactly snapshots, but you get the idea.)
• Windows-level (Volume Shadow Copy Services, or VSS)
• SAN-level

The difference between the levels is who’s doing the change tracking, and each level has its own advantages.

With SQL Server 2005’s snapshots, the DBA controls the whole process inside SQL Server Management Studio.  The snapshots appear as read-only databases, which can be really useful.  Say you automate the snapshot process and take a snapshot every morning at 8am, and then present that to your users as DatabaseName_8am.  Presto, if somebody drops an index or deletes records, they can go back to the 8am snapshot without calling the DBA.  Problem is, these snapshots aren’t useful for real backup – meaning, we can’t easily get ‘em off the database server.

With Windows-level snapshots using Volume Shadow Copy, the Windows admin controls the process inside their backup program of choice (Veritas, Microsoft DPM, etc).  Windows takes the snapshot and doesn’t care what’s on the drive.  The Wintel folks automate the process and shuffle the data off to tape.  Problem is, these snapshots aren’t useful for application-level restores, like letting our users query directly into the snapshot to see what changed.

With SAN-level snapshots, the SAN admin controls the process inside the SAN.  They take a snapshot of an entire array and present it to a backup server or shuffle it off to tape.  These combine the best of both worlds, because with solutions like EMC’s or NetApp’s, you can present the snapshot to your database servers or Exchange servers and query the snapshot just like it’s a live database.  One problem is that they cost an arm and a leg, and another issue is that they’re usually SAN-specific - meaning, each of your SANs will have its own snapshot technology, snapshot administration tools, and ways to go about using it.

Each of the snapshot technologies has its own pros and cons, and they’re all useful in different situations.

Technorati Tags: database snapshot, snapshot backup, snapshots, sql snapshot, vss