Posted by Erland Sommarskog on 04/19/07 21:34

MPD (mpd.jhb@gmail.com) writes:
> I am working through my 70-431 course, and this was mentioned a number of
> times. However, I see no point in doing this.
>
> Consider:
> I take snapshots hourly, on the hour. At 1.10pm, someone admits a major
> blunder, and tells me they dropped a table at 11am.
> I can now restore the 11am snapshot to a new DB and recover the table.
> But, to do this, I need to delete the other snapshots.

I haven't used snapshots much at all, but I did a quick read in Books
Online, and I don't think this is right.

What is correct is that if you decided to revert a snapshot, then all
other snapshots must be deleted. But in that case, at least newer
snapshots would be completely pointless.

But in the case of the big blunder, all you need to do is recreate the
table, possibly scripting it from the snapshot before the blunder, and the
insert the data over. Only the data after that shapshot was taken would
be lost.

The advantage with using snapshots for this sort of recovery is that
you can repair the blunder very quickly, as all data are online. There are
two important drawbacks:
1) Not up-to-the-point recovery.
2) There is an overhead for maintaining the shapshots. (Intially, the
snapshot is an almost empty sparse file. As pages are modified in
the source, pages are copied to the snapshot file.)

The tested and tried method for up-to-the-point recovery is of course
backing up the database and the transaction log regularly. But for a
huge database, making a full restore and apply logs could take quite
some time. And if you don't have the backup on local storage, the cost
for getting it onto the machine is also considerable.

> In fact, the only real use for snapshots I can see, is
> To make a snapshot of a mirrored / log shipped database so it can be
> used as a static report DB, OR
> To make a quick "backup" where a DBA needs to do some work quick and might
> risk dataloss through an error.

Yes, I think you got it right there. Snapshot is not a good solution
for recovery in general.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]