Bulk-Logged Recovery

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:

SELECT INTO.
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

Back up the currently active transaction log. For more information, see Transaction Log Backups.
Restore the most recent full database backup.
If differential backups exist, restore the most recent one.
Apply in sequence all transaction log backups created since the most recent differential or full database backup.
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

Transact-SQL

Enterprise Manager

SQL-DMO