misunderstanding #28: Several misunderstandings about the large capacity transaction log Recovery Model
A common DML operation can be "minimal log"
No. Only a small number of bulk operations can be "minimally logged" under the bulk transaction log recovery model, and the list of such operations can be Operations Can be minimally loggedFound it. 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.
