Page 157 - DCAP508_DATABASE_ADMINISTRATION

Unit 10: Database Backup and Restore

A database snapshot cannot be configured as a scalable shared database. Notes
FILESTREAM filegroups are not supported by database snapshots. If FILESTREAM
filegroups exist in a source database, they are marked as offline in its database snapshots,
and the database snapshots cannot be used for reverting the database.

!

Caution A SELECT statement that is executed on a database snapshot must not specify a
FILESTREAM column; otherwise, the following error message will be returned: Could
not continue scan with NOLOCK due to data movement.
Disk Space Requirements

Database snapshots consume disk space. If a database snapshot runs out of disk space, it is
marked as suspect and must be dropped. (The source database, however, is not affected; actions
on it continue normally.) Compared to a full copy of a database, however, snapshots are highly
space efficient. A snapshot requires only enough storage for the pages that change during its
lifetime. Generally, snapshots are kept for a limited time, so their size is not a major concern.
The longer you keep a snapshot, however, the more likely it is to use up available space. The
maximum size to which a sparse file can grow is the size of the corresponding source database
file at the time of the snapshot creation. If a database snapshot runs out of disk space, it must be
deleted (dropped).

Notes Except for file space, a database snapshot consumes roughly as many resources as a
database.

10.4.8 Creating a Database Snapshot

This topic describes some best practices for creating database snapshots. Following are some
best practices for naming database snapshots, timing when you create them, limiting their
number, and redirecting client connections to a snapshot.

Naming Database Snapshots

Before creating snapshots, it is important to consider how to name them. Each database snapshot
requires a unique database name. For administrative ease, the name of a snapshot can incorporate
information that identifies the database, such as:
The name of the source database.

An indication that the new name is for a snapshot.
The creation date and time of the snapshot, a sequence number, or some other information,
such as time of day, to distinguish sequential snapshots on a given database.
For example, consider a series of snapshots for the AdventureWorks2008R2 database. Three
daily snapshots are created at 6-hour intervals between 6 A.M. and 6 P.M., based on a 24-
hour clock. Each daily snapshot is kept for 24 hours before being dropped and replaced by a
new snapshot of the same name. Note that each snapshot name indicates the hour, but not
the day:

LOVELY PROFESSIONAL UNIVERSITY 151