Page 130 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 9.2 Recovery Models

SQL Server 2000 introduces the concept of recovery models for databases. Recovery models are
designed to simplify the administration of SQL Server 2000 databases. There are three recovery
models in SQL Server 2000—Full, Bulk-Logged, and Simple. System databases (including master,
MSDB, and tempdb) are set to the Simple Recovery model. All user databases, by default, are
created with the Full Recovery model (it should be noted that the Full Recovery model takes
affect once a complete database backup is performed). The recovery model may be changed once
the database is created.
The recovery model for a database incorporates the two most often used settings—Truncate Log
on Checkpoint and Select Into/Bulk copy.
1. Truncate Log on Checkpoint. In previous versions of SQL Server, this setting was selected
to automatically truncate the transaction log every time CHECKPOINT is activated for
the database.
2. Select Into/Bulkcopy. This setting was used in previous versions of SQL Server to perform
non-logged operations.
Following are the settings and their relation to the three recovery models.

Recovery Model Select Into / BulkCopy Truncate Log on Checkpoint
Full False False
Bulk-Logged True False
Simple True/False True
1. Simple Recovery Model: The simple recovery model minimizes administrative overhead
for the transaction log, because the transaction log is not backed up. The simple recovery
model risks significant work-loss exposure if the database is damaged. Data is recoverable
only to the most recent backup of the lost data. Therefore, under the simple recovery
model, the backup intervals should be short enough to prevent the loss of significant
amounts of data. However, the intervals should be long enough to keep the backup
overhead from affecting production work. Including differential backups in the backup
strategy can help reduce the overhead.
Generally, for a user database, the simple recovery model is useful for test and development
databases or for databases containing mostly read-only data, such as a data warehouse.
The simple recovery model is inappropriate for production systems where loss of recent
changes is unacceptable. In such cases, we recommend using the full recovery model.
2. Bulk-Logged Recovery Model: The full recovery and bulk-logged recovery models provide
greater protection for data than the simple recovery model. These recovery models rely
on backing up the transaction log to provide full recoverability and to prevent work loss
in the broadest range of failure scenario.
This recovery model bulk logs most bulk operations. It is intended solely as an adjunct to
the full recovery model. For certain large-scale bulk operations such as bulk import or
index creation, switching temporarily to the bulk-logged recovery model increases
performance and reduces log space consumption. Log backups are still required. Like the
full recovery model, the bulk-logged recovery model retains transaction log records until
after they are backed up. The tradeoffs are bigger log backups and increased work-loss
exposure because the bulk-logged recovery model does not support point-in-time recovery.
3. Full Recovery Model: SQL Server performs full transaction logging for any bulk load
operations if a database is in Full Recovery model. Transaction log backups should be
performed at regular intervals for maximum recoverability. This model provides the
safest mode of operation for production systems.

124 LOVELY PROFESSIONAL UNIVERSITY