Jaa

Considerations for Switching from the Simple Recovery Model

  • Artikkeli

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.

After Switching from the Simple 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.

After 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.

Changing the Recovery Model

To change the recovery model (Transact-SQL)

Use ALTER DATABASE, as follows:

USE master;

ALTER DATABASE database_name SET RECOVERY SIMPLE;

Note

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)

See Also

Concepts

Backup Under the Simple Recovery Model
Overview of the Recovery Models

Other Resources

Managing the Transaction Log

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005
New content:
  • Expanded the discussion of how to manage a switch to and from the simple recovery model.
Changed content:
  • Removed an erroneous note.
  • Moved topic from the "Backing Up and Restoring Databases" section.