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?
-
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.
-
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
-
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
-
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
-
It should exist on the same server as the source
database
-
contains only commited transaction at the point of which it
was taken. Uncommited chages are rollback
-
It is read only
-
You cannot drop files from a snapshot.
-
You cannot back up or restore snapshots.
-
You cannot attach or detach snapshots.
-
You cannot create snapshots on FAT32 file system or RAW
partitions.
-
Full-text indexing is not supported on database snapshots,
and full-text catalogs are not propagated from the source
database.
-
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.
-
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.
-
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.
-
Reverting is unsupported for read-only filegroups as well as
compressed filegroups. Attempts to revert to either of these
types of filegroups fail.