Page 131 - DCAP508_DATABASE_ADMINISTRATION

Unit 9: Database Recovery Models

Provides the normal database maintenance model for databases where durability of Notes
transactions is necessary.
Log backups are required. This model fully logs all transactions and retains the transaction
log records until after they are backed up. The full recovery model allows a database to be
recovered to the point of failure, assuming that the tail of the log can be backed up after the
failure. The full recovery model also supports restoring individual data pages.

Notes Under the full recovery and bulk-logged recovery models, log backups are essential.
If you do not want to take log backups, use the simple recovery model.
Difference between various recovery models
The following table summarizes the recovery models and backup types available with each
recovery model.
Recovery Model/ Backup Complete Differential Transaction Log File / Filegroup
Simple Required Allowed Not Allowed Not Allowed
Bulk-Logged Required Allowed Required Allowed
Full Required Allowed Required Allowed

9.3 Partial Database Restore

New functionality in SQL Server 2000 provides commands to restore a database backup partially.
If a database contains several filegroups, a single filegroup may be recovered using this new
functionality. Partial database restore operations provide a means to restore only certain parts
of the database, as needed.

Self Assessment

Fill in the blanks:

5. An SQL Server database’s basic data storage structure is 8 KB and is known as a
…………………….
6. ……………………. should be performed at regular intervals for maximum recoverability.
7. There are three recovery models in SQL Server 2000—……………………., Bulk-Logged
and Simple.
8. A database in ……………………. recovery model will have minimum logging for bulk
import operations.
9. Recovery models are designed to simplify the ……………………. of SQL Server 2000 databases.

9.4 How to View or Change the Recovery Model of a Database

!
Caution Before changing the recovery model of a database, you should understand the
impact of the change on your backup and restore strategy.

LOVELY PROFESSIONAL UNIVERSITY 125