Page 153 - DCAP508_DATABASE_ADMINISTRATION

Unit 10: Database Backup and Restore

at least once, the snapshot will grow to the size of the source database. If the disk begins to fill Notes
up, the snapshots compete with each other for disk space. If the disk drive fills up, write operations
to all the snapshots will fail.
Therefore, it is useful to know the typical update patterns for a database when planning how
much space is required during the planned lifespan of a snapshot. For some databases, the rate
of updates may be fairly constant; for example, an inventory database might have many of its
pages updated daily, making it useful to replace old snapshots daily or weekly. For other
databases, the proportion of updated pages may vary during the business cycle; for example, a
catalog database might be updated primarily quarterly, with only occasional updates at other
times; creating snapshots just before and after each quarterly update would be a logical strategy.
The pre-update snapshot would permit reverting if a significant update error occurs, and the
post-update snapshot could be used for report writing during the next quarter.
The following figure illustrates the effects of two contrasting update patterns on the size of a
snapshot. Update pattern A reflects an environment in which only 30 percent of the original
pages are updated during the life of the snapshot. Update pattern B reflects an environment in
which 80 percent of the original pages are updated during the life of the snapshot.
Figure 10.16: Effects of Two Contrasting update Patterns on the Size of a Snapshot

10.4.4 Metadata about Database Snapshots

For database snapshots, database metadata includes the source_database_id property, which is
stored in a column of the sys.databases catalog view. Generally, a database snapshot does not
expose metadata of its own, but it does expose metadata from its source database. This metadata
includes, for example, the data returned by the following statement:
USE <database_snapshot> SELECT * FROM sys.database_files
where <database_snapshot>is the name of a database snapshot.

The only exceptions are when the source database uses full-text search or database mirroring,
which disable themselves on a snapshot by altering some values in the snapshot’s metadata.

LOVELY PROFESSIONAL UNIVERSITY 147