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:
-
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.
-
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;
.gif) 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