Is Pseudo-Simple SQL Server Recovery a real thing?

See this article from Paul Randal, an expert on database recovery models: New script: is that database REALLY in the FULL recovery mode?

In particular this quote confirms the behavior of a "pseudo simple" recovery model:

...when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode until the log backup chain is established (this is commonly called being in ‘pseudo-Simple’).

Kimberly Tripp elaborates on the topic here: Database Maintenance Best Practices Part III – Transaction Log Maintenance

What that means is that SQL Server automatically clears inactive records from the transaction log once it knows that it no longer needs them. It no longer needs them to be stored in the log because no one is using the log

I don't totally agree with your statement "Nothing remotely simple about that" regarding the transaction log backup failing in the FULL recovery model prior to a FULL backup. That's just like the SIMPLE model (log backups not allowed). The error message is different because it's trying to tell the end user how to solve the problem.

I tried your test on SQL Server 2017, and running the copy-only backup consistently triggers an internal CHECKPOINT and clears the log (PercentFree increases for the log file).

On the initial run, PercentFree from the _log file is 65.04.
I ran the copy-only backup, and PercentFree jumped to 88.68.

I confirmed with the following extended events session that a CHECKPOINT was run at this time as well.

CREATE EVENT SESSION [checkpointage] ON SERVER 
ADD EVENT sqlserver.checkpoint_begin(
    ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.checkpoint_end(
    ACTION(sqlserver.database_id))
ADD TARGET package0.event_file(SET filename=N'checkpointage')
WITH (STARTUP_STATE=OFF)
GO

Pseudo-simple means that a database in the full recovery model will behave as though it is in the simple recovery model until the first full backup is taken (the last Log Sequence Number that was backed up is recorded).

In other words, a database that is in the full recovery model requires a valid backup chain, and a valid backup chain requires a full backup to kick things off. Then you can perform log and differential backups in parallel to your heart's content, each of which are based off this initial full backup.

As you saw in the scripts you ran, there was no backup chain established until you ran your full (copy-only) backup. Once that ran, you started a backup chain, but because you did a copy-only backup, you confused things.

Differential backups are a shortcut to reduce the number of log backups you need to restore to a specific point in time. They are dependent on a full backup that is not COPY ONLY, because the copy-only backup does not reset the differential bitmap used for tracking which extents were modified since the last full backup.

Had you done a standard full backup (without COPY ONLY), your differential would have succeeded, and the database would no longer be in the pseudo-simple state. It was confused further by the fact that a differential backup would also have failed on a database in the simple recovery model without a full (non-copy-only) backup to kick off the chain.

I (OP on the question) was at a loss on why, "Pseudo-Simple SQL Server Recover" was working as defined in multiple posts, but I was not seeing it on my system with these tests.

In the research stage, I put the database in SIMPLE and recreated the test, the log file used size grew the same as it did in FULL, this suggested something with Control Transaction Durability but further testing ruled this out.

After more research and testing I figured it out! (Thank you to a coworker who helped me problem solve.)

• We are using 50MB as the start size for 'model' which means RecoveryModel starts the same. (Default SQL 2017 is 8MB)
• This was creating 4 VLFs at about 12.5MB each
• The workload I am using in the test above, creates about 2MB of data in the logs with each run.
• I was running the test work load 3 to 5 times (6 to 10MB of logs), before doing the COPY ONLY backup.
• The VLF used space was growing, but as it never filled up the first VLF, it stayed at status 2, and nothing was released.
• When the test seemed to give unexpected results I dropped the database and started a new test.
• In a follow up test I ran the workload to create ~15MB of logs, then ran the COPY ONLY and used space fell to 2MB, with the first VLF returning to Status 0 and the second VLF holding the last 2MB
• This can be validated by using the code below in addition to above to modify and test for yourself.

.

USE [RecoveryModel]
GO
DBCC LOGINFO

Related info talking about VLF's: Too many VLFs - How do I truncate them?