Because of this behaviour, when a database is in full recovery model and the database is damaged in such a way that the transaction log is intact and undamaged, a tail-log backup can be taken which holds the last transactions that occurred in that database. Hence, assuming the database's transaction log is intact, it is possible to restore without data loss in case of a disaster.

h.tobisch (9/2/2011)

---

You write: "An operation is said to be minimally-logged if all it logs are the allocation changes."
In the case of a bulk insert this would mean either that a bulk insert can use only fresh allocation units (pages?) or that your statement must be rewritten to "logs which allocation units have been affected"

Whether minimal logging can occur for a table also depends on whether the table is indexed and, if so, whether the table is empty:

If the table has no indexes, data pages are minimally logged.

If the table has no clustered index but has one or more nonclustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty:
* If the table is empty, index pages are minimally logged.
* If table is non-empty, index pages are fully logged.

If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.

And a second question: what exactly cannot be recovered in case of minimal logging?

h.tobisch (9/2/2011)

---

Enjoyed the article.

And a second question: what exactly cannot be recovered in case of minimal logging?

If a disaster occurs to a database in bulk-logged recovery, even if the log file is intact and undamaged, it may not be possible to take a tail-log backup. To take a tail-log backup of a database in bulk-logged recovery where the data file is missing or damaged, there must have been no bulk-operations since the last log backup. Similarly, to restore a database in bulk-logged recovery to a point in time, that time must be within a log interval (time covered by a single log backup) in which no minimally logged operations occurred. If any minimally logged operations occurred within a log interval, the database can be restored only to the beginning or end of that log interval, not to a point in the middle.

Because of these limitations, having a database in bulk-logged recovery increase the chances of data loss in the case of a disaster. Hence it is more common to switch databases temporarily to bulk-logged recovery for certain operations (like index rebuilds) and then back to full recovery afterwards, than to have them in bulk-logged recovery permanently.
....
That said it's a very good idea to take a log backup after switching back to full recovery so that the current log interval does not contain any minimally-logged operations that could prevent tail-log backups or point-in-time recovery.