SQL Server 2005 offers a new tool, Database Snapshot, which helps protect against user errors. It's a great feature, in some ways. But Ravindra Okade cautions against relying on it as an all-purpose rescue device.

Like this article? We recommend

Database Snapshot (DB Snapshot for short) is a new tool offered by SQL Server
2005. Database snapshots can be used to protect against user errors, by creating
a "snapshot" of your data that you can refer to later if you need to
recover data or database objects that were accidentally (or even intentionally)
updated or dropped. While the feature is quite useful, it doesn’t provide
a 100% guarantee against user errors. This article explores Database Snapshot,
along with a discussion of why this feature falls short of its promise.

What Are DB Snapshots?

Simply put, DB snapshots are "online" backups; you’ve created
a copy of the database, available for read-only access. It’s very easy to
create and can be created very fast.

DB snapshots are primarily used to guard against user errors. If a user makes
a mistake while updating or deleting data, you can look in the snapshot, find
the original data, and use it to replace the changed data in the main database.
Here are some typical examples of user error scenarios:

A stored procedure has a bug—it deleted all rows instead of just the
required rows.

A user made a mistake while working with an application, and the application
didn’t have safeguards to prevent such mistakes.

A database operator or database administrator (heaven forbid) made a mistake
while working with SQL commands.

Note that DB snapshots do not replace the essential DB backups—DB
snapshots can exist only if data files of the source database are undamaged.
Also, DB snapshots are not to be confused with SQL Server 2005’s Snapshot
Isolation feature.