Page 151 - DCAP508_DATABASE_ADMINISTRATION

Unit 10: Database Backup and Restore

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse Notes
file is an essentially empty file that contains no user data and has not yet been allocated disk
space for user data. As more and more pages are updated in the source database, the size of the
file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is
updated over time, however, a sparse file can grow into a very large file.
The following figure illustrates a copy-on-write operation. The light gray rectangles in the
snapshot diagram represent potential space in a sparse file that is as-yet unallocated. On receiving
the first update to a page in the source database, the Database Engine writes to the file and the
operating system allocates space in the snapshot’s sparse files and copies the original page there.
The Database Engine then updates the page in the source database. The following figure 10.13
illustrates such a copy-on-write operation.

Did u know? Because database snapshots are not redundant storage, they do not protect
against disk errors or other types of corruption. Taking regular backups and testing your
restore plan are essential to protect a database. 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.
To the user, a database snapshot appears never to change, because read operations on a database
snapshot always access the original data pages, regardless of where they reside. If the page has
not yet been updated on the source database, a read operation on the snapshot reads the original
page from the source database. The following figure shows a read operation on a newly created
snapshot, whose sparse file accordingly contains no pages. This read operation reads only from
the source database.

Figure 10.14: Read Operation

LOVELY PROFESSIONAL UNIVERSITY 145