Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Bulk-Logged Recovery
Collapse the table of content
Expand the table of content

Bulk-Logged Recovery

SQL Server 2000

The Bulk-Logged Recovery model provides protection against media failure combined with the best performance and minimal log space usage for certain large-scale or bulk copy operations. These operations are minimally logged:


  • Bulk load operations (bcp and BULK INSERT).

  • CREATE INDEX (including indexed views).

  • text and image operations (WRITETEXT and UPDATETEXT).

In a Bulk-Logged Recovery model, the data loss exposure for these bulk copy operations is greater than in the Full Recovery model. While the bulk copy operations are fully logged under the Full Recovery model, they are minimally logged and cannot be controlled on an operation-by-operation basis under the Bulk-Logged Recovery model. Under the Bulk-Logged Recovery model, a damaged data file can result in having to redo work manually.

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. Point-in-time recovery is not supported.

In Microsoft® SQL Server™ 2000, you can switch between full and bulk-logged recovery models easily. It is not necessary to perform a full database backup after bulk copy operations complete under the Bulk-Logged Recovery model. Transaction log backups under this model capture both the log and the results of any bulk operations performed since the last backup.

The backup strategy for bulk-logged recovery consists of:

  • Database backups.

  • Differential backups (optional).

  • Log backups.

    Backing up a log that contains bulk-logged operations requires access to all data files in the database. If the data files are not accessible, the final transaction log cannot be backed up and all committed operations in that log will be lost.

To recover in the event of media failure

  1. Back up the currently active transaction log. For more information, see Transaction Log Backups.

  2. Restore the most recent full database backup.

  3. If differential backups exist, restore the most recent one.

  4. Apply in sequence all transaction log backups created since the most recent differential or full database backup.

  5. Manually redo all changes since the most recent log backup.

Important  If the active transaction log is lost (for example, due to hardware failure on the disk containing the transaction log files), all transactions in that log are lost. To prevent loss of the active transaction log, place the transaction log files on mirrored disks.

To create a database backup


Enterprise Manager


To create a transaction log backup


Enterprise Manager


To back up the transaction log when the database is damaged



To restore a database backup


Enterprise Manager


To apply a transaction log backup


Enterprise Manager


See Also

Restoring a Database to a Prior State

Transaction Logs


Using File or Filegroup Backups

© 2015 Microsoft