Page 150 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 10.4.2 How Database Snapshots Work

A database snapshot provides a read-only, static view of a source database as it existed at
snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled
back in a newly created database snapshot because the Database Engine runs recovery after the
snapshot has been created (transactions in the database are not affected).

Database snapshots are dependent on the source database. The snapshots of a database must be
on the same server instance as the database. Furthermore, if that database becomes unavailable
for any reason, all of its database snapshots also become unavailable.

Snapshots can be used for reporting purposes. Also, in the event of a user error on a source
database, you can revert the source database to the state it was in when the snapshot was created.
Data loss is confined to updates to the database since the snapshot’s creation. Also, creating a
database snapshot can be useful immediately before making a major change to a database, such
as changing the schema or the structure of a table.
Understanding how snapshots work is helpful though not essential to using them. Database
snapshots operate at the data-page level. Before a page of the source database is modified for the
first time, the original page is copied from the source database to the snapshot. This process is
called a copy-on-write operation. The snapshot stores the original page, preserving the data
records as they existed when the snapshot was created. Subsequent updates to records in a
modified page do not affect the contents of the snapshot. The same process is repeated for every
page that is being modified for the first time. In this way, the snapshot preserves the original
pages for all data records that have ever been modified since the snapshot was taken.
Figure 10.13: Copy-on Write Operation

144 LOVELY PROFESSIONAL UNIVERSITY