Bulk-Logged Recovery Model

• The bulk-logged recovery model is a special-purpose model that works in a similar manner to the full recovery model. The only difference is in the way it handles bulk data modification operations.
• With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log.
• The advantage of using the "Bulk-logged" recovery model is that your transaction logs will not get that large if you are doing bulk operations and it still allows you to do point in time recovery 

 

The situation with the bulk-logged recovery model is identical to the full recovery model if no minimally logged transactions are created in the database.  An example of a minimally logged transaction is a SELECT INTO operation.  Say that such a transaction occurred at 9:40 a.m.This transaction would be minimally logged, which means that SQL Server only records the changed database pages arising from the transaction.  It does not log every insert into the new table.  Again, say that a critical table was deleted at 9:50 a.m.  What happens now?  You can no longer restore the database to its state at 9:49 a.m, as the transaction log backup created at 10:00 a.m cannot be used to restore to a specific point in time.  The best you can do is to restore the database to its state at 9:30 a.m.  Whenever a transaction log backup contains one or more minimally logged transactions, you cannot use that backup to perform a point in time restore.

Why then would anyone use the bulk-logged recovery model?  The main reason is performance.  Let's use the example of a SELECT INTO operation, say creating a large table from the results of a query.  If using the full recovery model, the details of every insertion into the new table is logged, and the transaction log will consume more space.  If using the bulk-logged recovery model, only details of the modified data pages are logged, allowing for better performance.  As we have seen, this comes at the expense of being able to perform a point in time restore using the transaction log.

Here are some reasons why you may choose this recovery model:

• Data is critical, but you do not want to log large bulk operations
• Bulk operations are done at different times versus normal processing.
• You still want to be able to recover to a point in time

Type of backups you can run when the data is in the "Bulk-logged" recovery model:

• Complete backups
• Differential backups
• File and/or Filegroup backups
• Partial backups
• Copy-Only backups
• Transaction log backups

How to set Simple recovery mode

ALTER DATABASE dbName SET RECOVERY BULK_LOGGED

GO

Or 

Right click on DB --> Properties-->Options