(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
The BULK_LOGGED recovery model continues to confuse people…
Myth #28: various myths around the BULK_LOGGED recovery model.
28a) regular DML operations can be minimally-logged
Only a small set of bulk operations can be minimally-logged when in the BULK_LOGGED (or SIMPLE) recovery model. The list is in the Books Online topic Operations That Can Be Minimally Logged has the list. This is the 2008 R2 link – make sure to check the link for the version you’re running on.
28b) using the BULK_LOGGED recovery model does not affect disaster recovery
Firstly, if a minimally-logged operation has been performed since the last log backup, and one or more data files were damaged and offline because of the disaster, a tail-of-the-log backup cannot be performed and so all user transactions performed since the last log backup will be lost.
Secondly, if a log backup contains a minimally-logged operation, a point-in-time restore cannot be performed to any time covered by the log backup. The log backup can either not be restored, or be restored in its entirety (plus additional log backups if required) – i.e. you can restore to a point:
Before the beginning of that log backup
At the end of that log backup
After the end of that log backup
But you can’t restore to a point during that log backup.
28c) using the BULK_LOGGED recovery model also reduces the size of log backups
A log backup that includes a minimally-logged operation must backup the minimal amount of transaction log *and* all the data file extents changed by that operation – otherwise the restore of the log backup would not fully reconstitute the minimally-logged operation. This means that log backups are roughly the same size whether in the FULL or BULK_LOGGED recovery model.