• Article

Backup Under the Bulk-Logged Recovery Model

This topic is relevant for optimizing bulk operations on SQL Server databases that typically use the full recovery model.

The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data. Much of the description of backup under the full recovery model also applies to the bulk-logged recovery model. This topic looks only at considerations that are unique to the bulk-logged recovery model.

Note

For information about what operations are minimally logged under the bulk-logged recovery model, see Operations That Can Be Minimally Logged.

We recommend that you minimize your use of the bulk-logged recovery model. The best practice is to switch to the bulk-logged recovery model right before a set of bulk operations, perform the operations, and then immediately switch back to the full recovery model. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.

How the Bulk-Logged Recovery Model Works

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.

Bulk-changes bitmap identifies changed extents

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.

Backup Restrictions Under the Bulk-Logged Recovery Model

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.

Restoring Backups Under the Bulk-Logged Recovery Model

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