Reverting to a Database Snapshot
This topic discusses how reverting to a snapshot works, describes restrictions on reverting, and provides a link to the procedure for reverting. As an alternative to restoring an online database from backups, any user who has RESTORE DATABASE permissions on the source database can revert it to its state when a database snapshot was created. Reverting to a recent snapshot may be appropriate when some of the data in an online source database becomes damaged. However, be certain that the snapshot was created before the error and the database has not become corrupted. For example, reverting can reverse a recent serious user error, such as a dropped table.
Reverting overwrites updates made to the source database since the snapshot was created by copying the copy-on-write pages from the sparse files back into the database. Only the updated pages are overwritten. The revert operation then overwrites the old log file and rebuilds the log. Consequently, you cannot later roll the reverted database forward to the point of user error, and any updates to the database since the snapshot's creation are lost. The metadata of a reverted database is the same as the metadata at the time of the snapshot.
Reverting to a snapshot also drops all the full-text catalogs.
Restrictions on Reverting
Reverting is unsupported under the following conditions:
- The source database contains any read-only or compressed filegroups.
- Any files are offline that were online when the snapshot was created.
- More than one snapshot of the database currently exists.
Only the snapshot that you are reverting can exist..
Before reverting a database, consider the following:
- Reverting from a database snapshot is not intended for media recovery. Unlike a regular backup set, the database snapshot is an incomplete copy of the database files. If either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. Furthermore, even when possible, reverting if database corruption were to occur is unlikely to correct the problem.
- During a revert operation, both the snapshot and the source database are unavailable. The source database and snapshot are both marked "In restore." If an error occurs during the revert operation, when the database starts up again, the revert operation will try to finish reverting.
- Because a successful revert operation automatically rebuilds the log, Microsoft recommends that you back up the log before reverting a database. Although you cannot restore the original log to roll forward the database, the information in the original log file can be useful for reconstructing lost data.
- Reverting breaks the log backup chain. Therefore, before you can take log backups of the reverted database, you must first take a full database backup or file backup. Microsoft recommends a full database backup.
To revert to a database snapshot
See Also
Tasks
How to: View a Database Snapshot (SQL Server Management Studio)
Concepts
Database Snapshots
Dropping a Database Snapshot
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|