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.
Solution : We can run Log backup more frequently during a minimal logging operation.
For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, point-in-time recovery is not possible with bulk-logged model. Therefore, if you run transactions under the bulk-logged recovery model that might require a transaction log restore, these transactions could be exposed to data loss. To maximize data recoverability in a disaster-recovery scenario, we recommend that you switch to the bulk-logged recovery model only under the following conditions:
- Users are currently not allowed in the database.
- All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.
If you satisfy these two conditions, you will not be exposed to any data loss while restoring a transaction log that was backed up under the bulk-logged recovery model.
We recommend that:
- Before switching to the bulk-logged recovery model, you back up the log.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
- After performing the bulk operations, you immediately switch back to full recovery mode.
- After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.