SQL Server 2005 DB Snapshot: Imperfect Yet Useful?
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.