8月25日
Database Snapshot
A database snapshot is a read-only, static view of a database (called the source database). Each database snapshot is transactionally consistent with the source database at the moment of the snapshot's creation. When you create a database snapshot, the source database will typically have open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transactionally consistent.
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
工具 
說明
