Benefits of DB Snapshots
DB snapshots are a great feature that could provide the database administrator with a tool to protect the database against user errors. The DB snapshots are very easy to create. They can also be created very quickly, since no data is copied into the snapshot when it’s created. You could set up snapshots using timed scripts that would create snapshots at frequent intervals.
We can recover deleted and updated rows in the main database, using the data in the snapshot. We can also recover dropped tables, views, stored procedures, and functions. I won’t cover how we can do this, because it’s no different from having two databases and transferring data or objects between the two.
DB snapshots can also be used against errors during risky operations on the main database. The database administrator could create a snapshot just before such operations. In case of errors during the operation, the database can be returned to the state at the time of the snapshot’s creation, as with the following script:
RESTORE DATABASE Test FROM DATABASE_SNAPSHOT = ’TestSnapshot’
DB snapshots can be used as "frozen" views of the database; for example, for the following purposes:
- Making the frozen database available as a historic reporting tool.
- Preventing users from seeing data as it’s being updated (for security reasons).
- Preventing users from seeing volatile data while some batch operation is being performed.