Database Snapshot In SQL Server
A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation time, minus if any uncommitted transactions is their.
Database snapshots are dependent on the source database. The snapshots of a database must be on the same server instance as the database.
Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created.
Query to create Database Snapshot:-
CREATE DATABASE AW_Snapshot ON ( NAME = Advent, FILENAME = 'E:\AWDW2008.SS' ) AS SNAPSHOT OF AdventureWorks;
Reasons to take database snapshots include:-
- Maintaining historical data for report
- Using a mirror database that you are maintaining for availability purposes to offload reporting
- Safeguarding data against administrative error
- Safeguarding data against user error