Simple, Full, and Bulk-Logged Recovery Models with Log Chain Semantics

Learning outcomes

A recovery model does not schedule backups and it does not magically make data safe. It controls transaction-log maintenance and which recovery operations SQL Server can support. ServiceHub’s recovery design therefore has two layers: the database property (SIMPLE, FULL, or BULK_LOGGED) and the operational process that actually takes data/log backups before the disk fills or a disaster occurs.

1. Recovery model is transaction-log policy

SIMPLE automatically makes inactive log space reusable after checkpoints when no other reuse blocker exists; it does not mean “no transaction log.” FULL and BULK_LOGGED retain log records until the log archive point advances through log backups, subject to other blockers such as active transactions, replication or AG dependencies.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition,       SERVERPROPERTY('InstanceDefaultBackupPath') AS default_backup_path;GOSELECT name,recovery_model_desc,state_desc,log_reuse_wait_desc,       is_auto_close_on,is_auto_shrink_onFROM sys.databasesWHERE name IN (N'master',N'model',N'tempdb',N'ServiceHubLab');GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN (N'backup compression default',N'backup compression algorithm');GO

2. Prove that FULL without a usable backup chain is not enough

Use a second disposable database so this lesson can demonstrate the transition cleanly even if Lesson 1 has already initialized the main lab.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',1;DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';SELECT @root AS backup_root;USE master;IF DB_ID(N'ServiceHubRecoveryModelLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubRecoveryModelLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubRecoveryModelLab;END;CREATE DATABASE ServiceHubRecoveryModelLab;ALTER DATABASE ServiceHubRecoveryModelLab SET RECOVERY SIMPLE;GOCREATE TABLE ServiceHubRecoveryModelLab.dbo.ChangeLedger(change_id int IDENTITY PRIMARY KEY,changed_at datetime2(3) DEFAULT SYSUTCDATETIME(),note nvarchar(200));INSERT ServiceHubRecoveryModelLab.dbo.ChangeLedger(note) VALUES(N'SIMPLE baseline');GOALTER DATABASE ServiceHubRecoveryModelLab SET RECOVERY FULL;GOSELECT name,recovery_model_desc,log_reuse_wait_descFROM sys.databases WHERE name=N'ServiceHubRecoveryModelLab';GOBEGIN TRY  DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));  DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;  IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';  DECLARE @too_early nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryModelLab_too_early.trn';  BACKUP LOG ServiceHubRecoveryModelLab TO DISK=@too_early WITH INIT,CHECKSUM;END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS expected_error,ERROR_MESSAGE() AS expected_message;END CATCH;GO

The intentionally early log backup should fail because the log chain has not yet been initialized by a data backup after leaving SIMPLE. This is a crucial operational boundary: changing the recovery model is configuration; taking the data backup is what starts the recoverable log sequence.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',1;DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';SELECT @root AS backup_root;DECLARE @full nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryModelLab_full.bak';DECLARE @log1 nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryModelLab_log1.trn';BACKUP DATABASE ServiceHubRecoveryModelLab TO DISK=@full WITH INIT,CHECKSUM;INSERT ServiceHubRecoveryModelLab.dbo.ChangeLedger(note) VALUES(N'FULL protected change');BACKUP LOG ServiceHubRecoveryModelLab TO DISK=@log1 WITH INIT,CHECKSUM;SELECT name,recovery_model_desc,log_reuse_wait_descFROM sys.databases WHERE name=N'ServiceHubRecoveryModelLab';SELECT TOP (10) type,backup_start_date,backup_finish_date,first_lsn,last_lsnFROM msdb.dbo.backupsetWHERE database_name=N'ServiceHubRecoveryModelLab'ORDER BY backup_finish_date DESC;

3. FULL without log backups is a disk-consumption design error

In FULL recovery, checkpoints alone do not advance the log archive point. If routine log backups never occur, log_reuse_wait_desc commonly reports LOG_BACKUP once log backup is the limiting factor, and the physical log can keep growing as the workload changes data. Shrinking the file does not repair that operational defect.

SELECT d.name,d.recovery_model_desc,d.log_reuse_wait_desc,       ls.total_log_size_mb,ls.active_log_size_mb,ls.log_truncation_holdup_reasonFROM sys.databases AS dCROSS APPLY sys.dm_db_log_stats(d.database_id) AS lsWHERE d.name IN (N'ServiceHubRecoveryLab',N'ServiceHubRecoveryModelLab');GO

4. BULK_LOGGED changes recovery granularity, not your need for log backups

BULK_LOGGED is a variant of FULL that can minimally log qualifying bulk operations. The corresponding log backup can include changed data extents and therefore be much larger than expected. More importantly, if a log backup contains bulk-logged changes, you cannot choose an arbitrary STOPAT inside that affected backup; you recover to the end of the backup. If the data files containing the bulk changes are damaged/unavailable, even tail-log backup can be constrained.

SELECT name,recovery_model_desc FROM sys.databasesWHERE name=N'ServiceHubRecoveryModelLab';GOALTER DATABASE ServiceHubRecoveryModelLab SET RECOVERY BULK_LOGGED;SELECT name,recovery_model_desc FROM sys.databasesWHERE name=N'ServiceHubRecoveryModelLab';GO-- A real bulk-logged drill must record which operation was minimally logged,-- the exact log-backup interval, and the acceptable point-in-time limitation.ALTER DATABASE ServiceHubRecoveryModelLab SET RECOVERY FULL;GO

Do not flip to BULK_LOGGED reflexively before every data load. Benchmark the log-volume benefit for the exact operation and confirm that the temporary loss of arbitrary PITR inside that interval is compatible with the business RPO.

5. Recovery model is one input to RPO/RTO

Recovery Point Objective (RPO) is the maximum tolerable data-loss interval. Recovery Time Objective (RTO) is the target time to restore service. FULL with a 30-minute log-backup schedule cannot honestly claim a 5-minute RPO merely because SQL Server supports point-in-time recovery. Likewise, taking log backups every minute can create thousands of files and operational overhead without improving RTO if restore automation is weak.

Model changes also have downstream effects. SIMPLE is incompatible with log shipping and Always On Availability Groups. Changing FULL/BULK_LOGGED to SIMPLE breaks the continuity you rely on for log-based recovery strategies. A runbook must therefore treat recovery-model changes as controlled changes with a new backup baseline and restore-plan review.

6. Production judgment

Production judgment. Monitor log backup age, log_reuse_wait_desc, physical log growth and free storage. Alert on failures before RPO is breached. After switching from SIMPLE to FULL/BULK_LOGGED, take the required data backup and begin log backups immediately. Treat BULK_LOGGED as a deliberate temporary recovery tradeoff, not a generic performance mode.

Authoritative references

• Recovery models — SIMPLE, FULL and BULK_LOGGED semantics
• View or change the recovery model — required actions after model changes
• Transaction log architecture and management — logging and truncation model
• sys.dm_db_log_stats — log reuse and VLF evidence