Switching Recovery Models
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. |