"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.