Database Snapshots

In SQL Server 2005, an interesting new feature is available is Database snapshots.(Of course it is available on Enterprise Edition only )

It is a point-in-time, read-only , Virtual copy of the source database that is a snapshot of a particular source database can be created in a particular time.It is most useful if your database contains historical data like Quarterly sales, year-wise employees performance. This snapshots can be used for further reporting purposes. Multiple snapshots can be created from single source database for different point of times.


When you create a snapshot for a Source database, SQL Server create an empty Sparse file(is NTFS File),If there are uncommitted transactions are there, it will not copied to Snapshot, the pages before the transactions are copied to Sparse file. NTFS Sparse file will not hold the user data at the time of creation.


Whenever the source database is modified after the snapshot is created, the Copy-On-Write operation is began for every snapshots! At the time of creation of a NTFS Sparse file, SQL Server creates a bitmap file for every sparse file, with bit for every page of the source database to check the page is copied to snapshot or not. When a page updating is in progress, SQL Server checks this bit and if the page is not copied then it will copy the page to snapshot(s). This is called Copy-On-Write operation. The Read from snapshot operation first checks this bit for whether read it from snapshot or from source database. This bitmap is stored in cache so it is always available until the SQL Server shuts down or the database is closed. If any one this two happen, then the bitmaps are need to be reconstructed when the database startups.


The snapshots can not be backed up an restored.More over if any snapshots are available for a particular database, that DB can not be dropped. Snapshots can not be attached or detached,
For security aspects, the all the security constraints are inherited to snapshots. If you drop a user from source database it will not dropped from snapshot!



Need more about Database snapshots

• Chapter 4. Databases and Database Files from the book Inside Microsoft SQL
  Server 2005 : The Storage Engine by Kalen Delaney.

  Read More
  about Read / Write operations in snapshots.

  For
  further pros and cons of database snapshots read this.