Export (0) Print
Expand All

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.

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.

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.

To change the recovery model (Transact-SQL)

Use ALTER DATABASE, as follows:

USE master;

ALTER DATABASE database_name SET RECOVERY SIMPLE;

NoteNote

To change the default recovery model for new databases, use ALTER DATABASE to change the recovery model of the model database.

To change the recovery model (SQL Server Management Studio)

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft