644
Part V: Enterprise Data Management
As per the error message, you must drop all the database snapshots except the one that you
want to revert to.Using a database snapshot is not a replacement for your regular backup and restore strategy. For example, if there
is a disk failure that results in the loss of the source database, you cannot use a database snapshot to recover. You
need good backups. You can use database snapshots to supplement your restore strategy only if you need to quickly
restore a table that has been accidentally dropped or some rows have been deleted. It’s good practice to continue
to take regular backups and restore them to test the backups to protect your data and minimize data loss when a
disaster occurs.After a database has been rolled back to a database snapshot, the snapshot still exists
at the same point in time that it existed before the database was rolled back. This enables
the database to continue to function and changes to be accepted while retaining the
point-in-time state as part of the database snapshot. This can be extremely useful when
performing load testing against a test instance because changes can be made, tested, rolled
back, and tried again over and over without having to restore the entire database.Anyone who has rights to restore a database on the SQL Server also has the ability to
restore a database from a database snapshot. If database snapshots are in use on a produc-
tion system, great care should be given as to who should have rights to the
RESTORE DATABASE statement.Rolling back a database to a prior point using a snapshot is not an operation that should be
taken lightly. It requires that no users use the database and that the administrator is sure
that all objects should be rolled back. If only a subset of the database needs to be rolled
back, the database should not be restored from a database snapshot. Following is a partial
list of things that are rolled back:■ (^) Changes to data in tables
■ (^) Index creation and removal
■ (^) Table schema changes
■ (^) View schema changes
■ (^) Procedure code changes
■ (^) Messages in SQL Service Broker queues
■ (^) Any transactions that were not complete when the snapshot was taken
■ (^) Object permissions’ changes
■ (^) FILESTREAM data
■ (^) File Table objects
c24.indd 644c24.indd 644 7/31/2012 9:25:50 AM7/31/2012 9:25:50 AM
http://www.it-ebooks.info