1

Is the Bulk-Logged recovery model in SQL Server ever preferable to Simple if point-in-time restore is never needed? I have read many articles about transaction log models, but cannot find a clear answer to this. The database in question has a lot of activity during nightly loading, but the data doesn't change after that. I am doing a full backup daily. Wouldn't Simple be the best choice here, while also potentially using less storage?

Improve this question
1
  • 1
    BULK_LOGGED is more like FULL than it is like SIMPLE. For your scenario it sounds like SIMPLE is fine.
    – Jon Seigel
    Commented Mar 12, 2014 at 13:23

3 Answers 3

Reset to default
3

No, use simple

Bulk-Logged is a special case of FULL, not an enhanced version of SIMPLE.

  • Bulk logged requires log backups otherwise the transaction log grows (as per full). This takes more space.
  • Simple does not require log backups: the log will (normally) be cleared by the DB Engine.

Bulk logged with minimally logged operations also requires the data files to be present. Full does not because each row changes is logged. This introduces another complexity.

So use SIMPLE if you can restore to the last full database backup

If you have a nightly load, then using DIFF backups is probably enough to give you extra restore capabilities.

Note, I don't think I've ever used bulk logged recovery since SQL Server 6.5

Improve this answer
2

The Simple Recovery Model also participates in minimally logged operations:

Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery. This topic identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).

Improve this answer
0

It's simpel:

  • If you need point-in-time restore: Full recovery model
  • If you want some changes not to be logged but yet point-in-time restore: bulk-logged recovery model
  • If a dataloss since the last database backup is sufficient: simple recovery model

And yes: simple recovery means smaller a transaction log, no need for transaction log backups and because of that even less backup volume.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.