Changing the recovery model back to FULL does not reconnect the missing log history. A log backup chain needs continuous supported coverage from an established data-backup boundary. Test the recovery-model transition in a disposable database so the backup and restore consequences are visible before a real change.
Connect the Recovery Model With the Backup Process
FULL recovery supports log backups and point-in-time recovery when the required backups form a valid restore sequence. The setting alone does not establish that sequence or prove the files are available. The operating process must take, retain, and test the required data and log backups.
I review that process before changing recovery model for a load or maintenance task. Switching to SIMPLE breaks continuous log-backup coverage across that interval. Switching back to FULL does not recreate log records that are no longer available for the old sequence.
Older backups remain useful for recovery points their own sequence covers. The break does not make every historical backup worthless. It prevents treating the old and new periods as one uninterrupted chain. A recovery-model dropdown is convenient, but it does not include an undo button for missing log coverage. The practical question is which recovery points the retained files can actually reach.
Keep the log backup chain mapped to the restore sequence so a configuration change does not obscure which recovery paths remain valid.
Establish the First Data and Log Backups
Use a disposable SQL Server instance or an approved isolated test database. The folder below must exist on the server and be writable by the SQL Server service account. Use dedicated test filenames whose contents can be replaced safely, because WITH INIT overwrites the selected backup media's existing contents.
The setup creates a new database, sets FULL recovery, and takes a full backup followed by a log backup. GO separates CREATE DATABASE from later work in SSMS. These are planned test operations, not claims about measured backup duration or size.
I keep backup files separate from unrelated restore evidence during this experiment. A convenient path is not permission to overwrite a useful backup. Confirm the dedicated names before running the statements, and retain them until the restore behavior has been checked. The test's value is the visible sequence and its metadata, not merely a successful completion message.
CREATE DATABASE LogChainDemo;
GO
ALTER DATABASE LogChainDemo SET RECOVERY FULL;
BACKUP DATABASE LogChainDemo TO DISK='C:\SqlBackups\LogChainDemo-start.bak' WITH INIT,CHECKSUM;
BACKUP LOG LogChainDemo TO DISK='C:\SqlBackups\LogChainDemo-start.trn' WITH INIT,CHECKSUM;Observe the Break in the Log Backup Chain
The next block switches to SIMPLE and then back to FULL. It attempts a log backup before establishing the new required data-backup boundary. TRY/CATCH returns the error your server produces, making the failure path part of the demonstration without inventing its observed output.
The metadata query shows last_log_backup_lsn from sys.database_recovery_status. Read it as recovery metadata associated with the database's current state. Do not use one non-NULL value as proof that every necessary file is retained or that the intended point-in-time restore works.
What backup action must follow the return to FULL? A new full or differential database backup establishes the required boundary before subsequent log backups can support the new sequence. Until that is done, the new FULL setting has not delivered the intended log-backup recovery path. Check the actual failure and current metadata rather than assuming the setting change completed the recovery transition. The BACKUP LOG in the next block is meant to fail, and the CATCH shows the error.
ALTER DATABASE LogChainDemo SET RECOVERY SIMPLE;
ALTER DATABASE LogChainDemo SET RECOVERY FULL;
SELECT database_id,last_log_backup_lsn
FROM sys.database_recovery_status WHERE database_id=DB_ID(N'LogChainDemo');
BEGIN TRY
BACKUP LOG LogChainDemo TO DISK='C:\SqlBackups\LogChainDemo-gap.trn' WITH INIT,CHECKSUM;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
Start a New Log Backup Chain Deliberately
The next block takes a new full backup, then a log backup. A suitable new differential backup is another supported way to establish the boundary, with its own required full-backup base for restore. The demonstration uses a new full to keep the restore starting point straightforward.
This action starts subsequent coverage. It does not bridge the earlier SIMPLE interval with invented log backups. Keep that gap explicit in the recovery review. Rows and changes represented in the new full are available through that new starting point, while earlier point-in-time choices depend on the backups that actually cover them.
Confirm that the routine log-backup job resumes and that its files are retained. A one-time demonstration log backup does not establish an operational schedule. Also review monitoring for backup failures, because FULL recovery without functioning log backups can create both recovery and log-space problems. The model and the schedule need to operate together after the change.
BACKUP DATABASE LogChainDemo TO DISK='C:\SqlBackups\LogChainDemo-new.bak' WITH INIT,CHECKSUM;
BACKUP LOG LogChainDemo TO DISK='C:\SqlBackups\LogChainDemo-new.trn' WITH INIT,CHECKSUM;Read the History Without Treating It as a Restore
msdb.dbo.backupset records backup metadata, including first_lsn and last_lsn. Keep backup type, time, and database-backup LSN beside those values. LSN relationships help describe the sequence, but interpreting them is not a replacement for testing the actual files.
The following query lists the demonstration history in completion order. History can remain after a file is removed, so confirm file availability independently. Backup checksums and verification checks also have narrower purposes than a complete restore followed by integrity validation.
Restore the new sequence to another test database with separate file paths when performing the recovery rehearsal. Validate the intended recovery point and database contents. Do not overwrite the demonstration source merely to prove the restore works. The supported restore sequence, its required base backup, and the actual retained log files determine the reachable points.
SELECT backup_start_date,backup_finish_date,type,first_lsn,last_lsn,
database_backup_lsn,is_copy_only
FROM msdb.dbo.backupset
WHERE database_name=N'LogChainDemo'
ORDER BY backup_finish_date,backup_set_id;Make the Recovery Decision Before the Setting Change
Review the required recovery-point objective before switching models. If uninterrupted point-in-time coverage is required, a SIMPLE interval conflicts with that requirement. Do not adopt the change merely because it promises a convenient loading behavior in another context.
Record the new backup boundary and any coverage gap after an approved change. Keep the old backups under their retention policy and verify the new schedule. Cleanup of the isolated test database and dedicated media belongs after the rehearsal evidence is retained.
The log backup chain is a relationship among real backups, not a status implied by the word FULL. Establish the required data-backup boundary, maintain the subsequent log sequence, and test the restore path. That is what turns the recovery-model setting into a usable recovery capability.
Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Undo Human Errors in SQL Server: SQL in Sixty Seconds #109: Point in Time Restore.
FULL recovery is not a complete restore plan, it is a model that needs an established and maintained backup chain.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
