Page 154 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 10.4.5 Typical Uses of Database Snapshots

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.
Clients can query a database snapshot, which makes it useful for writing reports based on the
data at the time of snapshot creation. Also, if the source database later becomes damaged, you
can revert the source database to the state it was in when the snapshot was created.

Notes Database snapshots are available only in SQL Server Enterprise.

10.4.6 Reasons to take Database Snapshots

It include:

Maintaining historical data for report generation.
Because a database snapshot provides a static view of a database, a snapshot can extend
access to data from a particular point in time. For example, you can create a database
snapshot at the end of a given time period (such as a financial quarter) for later reporting.
You can then run end-of-period reports on the snapshot. If disk space permits, you can also
maintain end-of-period snapshots indefinitely, allowing queries against the results from
these periods; for example, to investigate organizational performance.

Using a mirror database that you are maintaining for availability purposes to off-load
reporting.
Using database snapshots with database mirroring permits you to make the data on the
mirror server accessible for reporting. Additionally, running queries on the mirror
database can free up resources on the principal. Safeguarding data against administrative
error.
Before doing major updates, such as a bulk update or a schema change, create a database
snapshot on the database protects data. If you make a mistake, you can use the snapshot to
recover by reverting the database to the snapshot. Reverting is potentially much faster for
this purpose than restoring from a backup; however, you cannot roll forward afterward.

Notes Database snapshots are dependent on the source database. Therefore, using database
snapshots for reverting a database is not a substitute for your backup and restore strategy.
Performing all your scheduled backups remains essential. If you must restore the source
database to the point in time at which you created a database snapshot, implement a
backup policy that enables you to do that.
Safeguarding data against user error.
By creating database snapshots on a regular basis, you can mitigate the impact of a major
user error, such as a dropped table. For a high level of protection, you can create a series
of database snapshots spanning enough time to recognize and respond to most user errors.
For instance, you might maintain 6 to 12 rolling snapshots spanning a 24-hour interval,

148 LOVELY PROFESSIONAL UNIVERSITY