Export (0) Print
Expand All

Considerations for Switching from the Full or Bulk-Logged Recovery Model

Updated: 5 December 2005

A database can be switched to another recovery model at any time. If a switch occurs during a bulk operation, the logging of the bulk operation changes accordingly.

Switching between full and bulk-logged recovery models is useful before and after large bulk operations. The full recovery model, which fully logs all transactions, is intended for normal use. The bulk-logged recovery model is intended to be used temporarily during a large bulk operation—assuming that it is among the bulk operations that are affected by the bulk-logged recovery model (for more information, see Minimally Logged Operations). If you switch between the full and bulk-logged recovery models during a bulk operation, logging of the bulk operation changes accordingly.

ms190203.note(en-US,SQL.90).gifNote:
Some features such as database mirroring require that the database remain in the full recovery model.

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:

  • Users are currently not allowed in the database.
  • No modifications are made during bulk processing that are not recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

We recommend that:

  • Before switching to the bulk-logged recovery model, you back up the log.
    This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
  • After performing the bulk operations, you immediately switch back to full recovery mode.
  • After switching back to the full recovery model, you back up the log again.

The following figure illustrates these recommendations.

Recommend process for using bulk-logged recovery

When switching between two recovery models, your backup strategy remains the same: continue performing periodic database, log, and differential backups.

When switching from full to bulk-logged recovery, no additional action is necessary to protect your data. Immediately after you switch from bulk-logged to full recovery, back up the log. This fully protects your data and enables point in time recovery.

Switching from the full or bulk-logged recovery to simple recovery is possible, but uncommon.

Back up the transaction log just before switching to the simple recovery model, to permit recovery to that point. Backing up the log is not supported under the simple recovery model, so, after switching, discontinue any scheduled jobs for backing up the transaction log. For more information, see How to: Change Maintenance Tasks in the Maintenance Plan Wizard.

To change the recovery model (Transact-SQL)

Use ALTER DATABASE, as follows:

  • To set the database to the full recovery model:
    USE master;
    ALTER DATABASE database_name SET RECOVERY FULL;
  • To set the database to the bulk-logged recovery model:
    USE master;
    ALTER DATABASE database_name SET RECOVERY BULK_LOGGED;
ms190203.note(en-US,SQL.90).gifNote:
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)

Release History

17 July 2006

New content
  • Added Transact-SQL examples of setting the recovery model.

5 December 2005

New content
  • Added figure.
Changed content:
  • Expanded discussion of switching between full and bulk-logged recovery.
  • Moved topic from the "Backing Up and Restoring Databases" section.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft