Bulk-logged Recovery Model

The bulk-logged recovery model is designed for intermittent use to improve the performance of bulk imports of large amounts of data. It’s practically the same as the full recovery model with the only exception that under the bulk-logged recovery model some operations are logged minimally. These operations are:

• Bulk import operations (bcp, INSERT … SELECT, and BULK INSERT).
• SELECT INTO operations.
• Partial updates to large value data types using the WRITE clause in the UPDATE statement when inserting or appending new data. Please note that minimal logging is not used when existing values are updated.
• Inserting or appending new data into the text, ntext, and image data type columns using the WRITETEXT and UPDATETEXT statements. Please note that minimal logging is not used when existing values are updated.
• If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged regardless of whether the operation is executed online or offline. (Minimally logged index operations are as follows: CREATE INDEX, ALTER INDEX REBUILD or DBCC DBREINDEX and DROP INDEX).

Though the bulk-logged recovery model reduces log space usage by using minimal logging for most bulk-logged operations, it is recommended to temporarily switch to bulk-logged recovery model right before performing these operations and then immediately switch back to the full recovery model. This allows to minimize the use of bulk-logged recovery model.

There is the risk of data loss for these bulk-copy operations because bulk-logging operations prevent recapturing changes on a transaction-by-transaction basis. The point-in-time recovery with the bulk-logged recovery model is not possible because none of the minimally logged operations can be restored.

Let’s consider another scenario:

This picture shows that the failure occurs at 15:00, but before this a minimally logged operation took place and the next transaction log backup (marked yellow, at 16:00) contains bulk-logged changes. This means that point-in-time recovery is possible only up to the previous log backup. If you try to restore your database to later point-in-time you’ll get the following errors:

This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

RESTORE LOG is terminating abnormally

So the best sequence of actions in this case is to restore the database to its state as of 14:00. All other changes will be irretrievably lost.

Here’s how to do it:

RESTORE DATABASE your_database FROM DISK = 'full_10_00.bak' WITH NORECOVERY, REPLACE
RESTORE LOG your_database FROM DISK = 'log_12_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH RECOVERY

It is also important to mention that under the bulk-logged recovery model if a log backup covers any bulk operations it contains both log records and the data pages that were changed by bulk operations.  Also, if read/write database is changed to read-only access after a bulk-logged operation, subsequent log backups might capture more data than necessary. This is because the data file cannot be updated to track which data extents were changed by a bulk-logged operation.

Use the following command to set bulk-logged recovery model :

ALTER DATABASE your_database SET RECOVERY BULK_LOGGED