Categories
Sql Server

Database Snapshot In SQL Server

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:-

  1. Maintaining historical data for report
  2. Using a mirror database that you are maintaining for availability purposes to offload reporting
  3. Safeguarding data against administrative error
  4. Safeguarding data against user error