Export (0) Print
Expand All

Switching Recovery Models

SQL Server 2000

You can switch a database from one recovery model to another in order to meet changing business needs. For example, a mission-critical online transaction processing (OLTP) system requires full recoverability but periodically undergoes bulk load and indexing operations. The recovery model for the database can be changed to Bulk-Logged for the duration of the load and indexing operations and then returned to Full Recovery. This increases performance and reduces the required log space while maintaining server protection.

Note  Switching recovery models during a bulk load operation is permitted. The logging of the bulk operation changes appropriately.

The following table indicates what action to take when switching from one recovery model to another.

From To Action Description
Full Recovery Bulk-Logged Recovery No action Requires no change in backup strategy. Continue to perform periodic database, log, and (optionally) differential backups.
Full Recovery Simple Recovery Optionally back up the transaction log prior to the change Executing a log backup immediately before the change permits recovery to that point. After switching to the simple model, stop executing log backups.
Bulk-Logged Recovery Full Recovery No action Requires no change in backup strategy. Recovery to any point in time is enabled after the next log backup. If point-in-time recovery is important, execute a log backup immediately after switching.
Bulk-Logged Recovery Simple Recovery Optionally back up the transaction log prior to the change Executing a log backup immediately before the change permits recovery to that point. After switching to the simple model, stop executing log backups.
Simple Recovery Full Recovery Back up the database after the change Execute a database or differential backup after switching to the Full Recovery model. Begin executing periodic database, log, and (optionally) differential backups.
Simple Recovery Bulk-Logged Recovery Back up the database after the change Execute a database or differential backup after switching to the bulk-logged model. Begin executing periodic database, log, and (optionally) differential backups.

To set or change the recovery models

Enterprise Manager

Show:
© 2014 Microsoft