(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
No.
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
No.
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
No.
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.
2 thoughts on “A SQL Server DBA myth a day: (28/30) BULK_LOGGED recovery model”
“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.”
I was under the impression that you could restore to a point in time up until the minimally-logged operation occurred.
No. You can restore to any point before the time covered by that log backup, or any time afterwards, but not any time covered by the log backup as the backup contains data extents and there is no way for the restore to know when to restore some or all of those data extents or not. That log backup is all or nothing. Easy to test it for yourself.