Database Snapshot in Sql Server 2005

How to ?  

Run the following Sql codes to create the snapshot CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME = 
'C:\central data\central data\snapshots\data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;

Why do the snapshot? 

  1. can be used to maintain historical data
    how does the data look like at  a particular point in time such as the end of every quarter or month which can be used for historical reporting.
  2. It serves a read-only mirror of the data
    You can offload your reporting task to the snapshot as an availability manuever and free up the main database from intensive read only query
  3. safe guard data from administrative error
    database can be reverted from snapshot in case of administrative error schema changes, bulk insert and the likes. aside from that you can't delete, drop or detach the  source database without first deleting its snapshots
  4. Safe guard data from user error
    You can easily recover unwanted user error from the snapshot such as deletion and dropeed tables. Snapshot provides faster recovery as compared from recovering from the backup
Limitations
  1. It should exist on the same server as the source database
  2. contains only commited transaction at the point of which it was taken. Uncommited chages are rollback
  3. It is read only
  4. You cannot drop files from a snapshot.
  5. You cannot back up or restore snapshots.
  6. You cannot attach or detach snapshots.
  7. You cannot create snapshots on FAT32 file system or RAW partitions.
  8. Full-text indexing is not supported on database snapshots, and full-text catalogs are not propagated from the source database. 
  9. A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots. 
  10. A snapshot always reflects the state of filegroups at the time of snapshot creation: online filegroups remain online, and offline filegroups remain offline. For more information, see "Database Snapshots with Offline Filegroups" later in this topic.
  11. If a source database becomes RECOVERY_PENDING, its database snapshots may become inaccessible. After the issue on the source database is resolved, however, its snapshots should become available again.
  12. Reverting is unsupported for read-only filegroups as well as compressed filegroups. Attempts to revert to either of these types of filegroups fail.
Ebook Download
View all
Learn
View all