SQL Server myth: Misconceptions about the large-capacity transaction log Recovery model

Misunderstanding #28: Several misunderstandings about the large capacity transaction log recovery model

A common DML operation can be "minimal log"

No. In the bulk transaction log recovery model, only a small number of bulk operations can be "minimally logged", and a list of such operations can be found in operations that can minimally logged. This is a list for SQL Server 2008, and for different versions of SQL Server, make sure that you see the correct list.

Use of the bulk transaction log recovery model does not affect disaster recovery

First, the log is not backed up after the last transaction log backup, and because of a disaster causing the data file to be corrupted, the tail-end logs cannot be back up in this mode, so if a disaster occurs, all transactions from the last log backup will be lost.

In addition, this log backup cannot be used to restore to a specific point in time if the "minimum log" operation is included in the bulk transaction log recovery model. Therefore, this log backup will not be all restored, or not at all, that is, can only be restored to the following points of time:

Point in time before log backup starts

Point in time at the end of a log backup

Point in time after the end of a log backup

Anyway, you can't restore the database to a point in time within the log coverage.

c) Use of the bulk transaction log recovery model reduces the size of log backups

No.

When bulk transaction log backups are backed up significantly, the data area (Extent) that is modified by the bulk transaction log backup is backed up in order to be available for restore. This means that the bulk transaction log recovery model and the full recovery model are backed up almost as much as the log backup.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/