SQL Server Recovery – Is Pseudo-Simple a Real Thing?

"Pseudo-Simple SQL Server Recovery" is term and scenario, I was just made aware of in a (now deleted) comment for the new question SQL Server Truncates Transaction Logs with Copy Only Backups

I went to the post Pseudo-Simple SQL Server Recovery Model October 7, 2019 by Rajendra Gupta and using some of the code there and some of my own did some testing.

Create the database (Rajendra's code)

CREATE DATABASE RecoveryModel;

and Validate it is in Full (Rajendra's code)

SELECT name, 
    recovery_model_desc
FROM sys.databases
WHERE name = 'RecoveryModel';

Do some work (Rajendra's code, modified slightly)

Use RecoveryModel
CREATE TABLE test(id INT);
GO 
INSERT INTO test
VALUES(1);
GO 5000

See how much log space is used (my code)

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

We find that there log is filling. Run the work again and check the size, the log grows, no surprise.

Try running t-log (My code)

BACKUP LOG [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.trn' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD 
GO

It fails with message:

Msg 4214, Level 16, State 1, Line 8

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 8

BACKUP LOG is terminating abnormally.

Nothing remotely simple about that, if you try to backup a database in Simple recovery. you get the message

Msg 4208, Level 16, State 1, Line 19

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Run a copy only backup (my code)

BACKUP DATABASE [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.bak' WITH NOFORMAT, INIT, COPY_ONLY,  
NAME = N'RecoveryModel-Full Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It runs fine, check the log space and it has not shrunk. Run the work load a few more time, and the log space continues to grow. Run a t-log backup and it continues to fail.

Run a differential backup (My code)

BACKUP DATABASE [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.dif' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  
NAME = N'RecoveryModel-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It fails just like the t_log

Msg 3035, Level 16, State 1, Line 13
Cannot perform a differential backup for database "RecoveryModel", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

So what is "Pseudo-Simple" about this? The logs grow, t-log & differnatinal backups fail. You have a database in Full recovery, with no full backup.

Edit it looks like there is something specific to servers in my build that is causing results different than what everyone else is seeing. I have accepted Josh's answer.