Bulk-Logged

The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.

So once again, based on the information above it looks like the Full Recovery model is the way to go. Given the flexibility of the full recovery model, why would you ever select any other model? The following factors will help you determine when another model could work for you:

Select Simple if:

• Your data is not critical.
• Losing all transactions since the last full or differential backup is not an issue.
• Data is derived from other data sources and is easily recreated.
• Data is static and does not change often.
• Space is limited to log transactions. (This may be a short-term reason, but not a good long-term reason.)

Select Bulk-Logged if:

• Data is critical and no data can be lost.
• You always need the ability to do a point-in-time recovery.
• Bulk-logged activities are intermixed with normal transaction processing.
• You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.

Select Full if:

• Data is critical and no data can be lost.
• You always need the ability to do a point-in-time recovery.
• Bulk-logged activities are intermixed with normal transaction processing.
• You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.

Switching recovery models

For some databases, you may need to use a combination of these recovery models. Let's say you have a critical system and you cannot afford to lose any data during daily operations; but during off hours there are maintenance tasks and data loads that use way too much transaction log space to log every transaction. In a case like this, you may want to switch recovery models prior to your maintenance tasks. This can be automated using T-SQL in the job that runs your maintenance or data load tasks. After the maintenance task is completed, the recovery model can be switched back again.

Switching between full and bulk-logged models is probably the best scenario for changing recovery models and also the safest and easiest. You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set.

How to change recovery models

There are two options that can be used to switch recovery models.

• Enterprise Manager

• Right click on the database name, select Properties, select the Options tab and select recovery model from the drop-down list. Selecting OK will change the recovery model immediately.

• T-SQL

• ALTER DATABASE Northwind SET RECOVERY FULL GO

Summary

As you can see, the three recovery models each have a different purpose. If you are still unsure which recovery model to select, I recommend using the full recovery model and issuing both full and transaction log backups throughout the day. It becomes a little more complex to do both full and transaction log restores, but in case there is a failure in the middle of the day, you will appreciate the flexibility of point-in-time recovery.

Reference: http://searchsqlserver.techtarget.com/tip/Selecting-a-SQL-Server-recovery-model?ses=1090912