Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost.

To track the data pages, a log backup operation relies on a bulk-changes bitmap page that contains a bit for every extent. For each extent updated by a bulk-logged operation since the last log backup, the bit is set to 1 in the bitmap. The data extents are copied into the log followed by the log data. The following illustration shows how a log backup is constructed.

Important:
Under the full or bulk-logged recovery models, until the first full backup, automatic checkpoints truncate the unused part of the transaction log, unless other factors are keeping log records active. After the first full backup, truncation requires backing up the transaction log. For information about factors that delay truncation, see Factors That Can Delay Log Truncation.

Under the bulk-logged recovery model, the following backup restrictions exist:

• If a filegroup that contains bulk-logged changes is made read-only before a log backup is performed, all subsequent log backups contain the extents changed by the bulk-logged operations as long as the filegroup remains read-only. Such log backups are larger and take longer to complete than under the full recovery model.
  To avoid this situation, before you make the filegroup read-only, switch the database to the full recovery model and back up the log. Then make the filegroup read-only.
  
• If bulk operations were performed since the last log backup, bulk changes exist in the database. In this case, all files must be either online or defunct when the log backups are performed. This is because backing up a log that contains bulk-logged operations requires access to the data files that contain the bulk-logged transactions.
  

For information about restore restrictions, see Restore Under the Bulk-Logged Recovery Model.

Setting a Database to Read-Only After Bulk-Logged Transactions

Under the bulk-logged recovery model, log backups operate correctly when a database contains bulk-logged changes. However, if a 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. All later log backups contain the same information.

Best practice   Before you change the database to read-only, switch to the full recovery model and take the log backup. Then make the database read-only. In practice, taking log backups of a read-only database does not make sense. Instead, after the database becomes read-only, take either a full database backup or a complete set of file backups. For information about how to switch the recovery model, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.

Note:
For information about how to back up a read-only database, see Backing Up Read-Only Databases.

For information about how to restore backups of a bulk-logged recovery model database, see Restore Under the Bulk-Logged Recovery Model.

Concepts

Other Resources

Help and Information