Page 132 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 9.4.1 To View or Change the Recovery Model of a Database

1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine,
in Object Explorer, click the server name to expand the server tree.
2. Expand Databases, and, depending on the database, either select a user database or expand
System Databases and select a system database.
3. Right-click the database, and then click Properties, which opens the Database Properties
dialog box.
4. In the Select a Page pane, click Options.
5. The current recovery model is displayed in the Recovery model list box.
6. Optionally, to change the recovery model select a different model list. The choices are
Full, Bulk-logged, or Simple.

9.4.2 Considerations for Switching from the Simple Recovery Model

A database can be switched to another recovery model at any time. However, switching from
the simple recovery model is unusual. Be aware that if you switch to the full recovery model
during a bulk operation, the logging of the bulk operation changes from minimal logging to
full logging, and vice versa.

9.4.3 Considerations for Switching to the Full Recovery Model

If you must switch from the simple recovery model to the full recovery model, we recommend
that you:

1. Immediately after you complete the switch to the full recovery model or bulk-logged
recovery model, take a full or differential database backup to start the log chain.
The switch to the full or bulk-logged recovery model takes effect only after the first data
backup.
2. Schedule regular log backups and update your restore plan accordingly.
Log backups are an integral and fundamental aspect of the full and bulk-logged recovery
models. Log backups allow the transaction log to be truncated. If you do not back up the
log frequently enough, the transaction log can expand until it runs out of disk space.

9.4.4 Considerations for Switching to the Simple Recovery Model

If you switch from the full or bulk-logged recovery model to the simple recovery model, you
break the backup log chain. Therefore, we strongly recommend that you back up the log
immediately before switching, which allows you to recover the database up to that point. After
switching, you need to take periodic data backups to protect your data and to truncate the
inactive portion of the transaction log.
9.4.5 Switching between Full and Bulk-Logged Recovery

For a database that uses full recovery, switching to the bulk-logged recovery model temporarily
for bulk operations improves performance. However, if data loss is unacceptable, to prevent
data loss, we recommend that you switch to the bulk-logged recovery model only under the
following conditions:

126 LOVELY PROFESSIONAL UNIVERSITY