Why doesn't simple recovery model support log backup or tail log backup?

Question

Lets say there is a crash then we can use the full backup and a tail log backup - because if there was checkpoint then data would already be written to the database data file and flushed from the transaction log. Where as if there was no checkpoint yet then logs will still be there in the transaction log file. So is recovery possible in this recovery model?

Answer 1

The logs written between the FULL backups and the crash will be overwritten at some point
They are not saved with BACKUP LOG.

My bold describing Simple

Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions.

From SQL Server docs

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

In theory, a crash happening just after a full backup that has just a few transactions might work, if the LSNs match.

In practice, this is almost never guaranted to happen so for consistency (and false hope) better to disallow this in simple recovery model

Answer 2

Besides the reason already mentioned by gbn, when the log could be already overwritten since the last full backup, there is another reason to not to permit tail-of-the-log in simple recovery model.

When your database is in simple recovery model some operations are minimally logged. Here you can see the coplete least of this operations Operations That Can Be Minimally Logged, and here the conditions that are required: The Data Loading Performance Guide

To support high-volume data loading scenarios, SQL Server implements minimally logged operations. Unlike fully logged operations, which use the transaction log to keep track of every row change, minimally logged operations keep track of extent allocations and metadata changes only.

I highlighed the important part that will help you to understand that your cases are not complete. Independently from a checkpoint, there could be minimally logged operations that filled the log with "allocation page" only, without writing actual data in the log, and in this case there is no sense in tail-of-the-log backup.

Imagine that crash happens right after a minimally logged operation and data disk in no more accessible. If you try to do a log backup now, it should also access the data file(s) to backup the extents allocated by minimally logged operation, but it cannot be done.

This situation can happen only in simple and bulk logged recovery models, in full model the log contains all the information that server needs to reconstitute any operation.

In simple recovery model, even if it were permitted to take such a backup, there could be no possibility ro restore from this backup.

In case of bulk logged model + minimally logged operations you can try to do this kind of tail-of-the-log backup, but 2008 server and before will not permit you to take such a backup. Starting with 2008 R2 even if you will be able to do this tail-of-the-log backup, and even restore from it, this will result in inconsistent database because newly allocated pages will not contain correct data. So you can think that tail-of-the-log backup is not permitted not only in simple recovery model ma also in full after the switching to bulk logged + minimally logged operation + data file not accessible.