Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Reasons for Transaction Log Backup Chain breaking

04 August,2022 by Tom Collins

The SQL Server transaction log backup chain aka log chain is the series of sequential transaction log backups related to a database. The log backups are related to each other and are represented through LSN . Breaking the transaction log chain will limit the restore point of the backups. 

An example:

Database1 has a backup schedule of a FULL Backup once a day and transaction log backups every hour .    During the day a developer decides to change the Recovery model from FULL to SIMPLE.   This action breaks the log chain.    Let's say the developer changed the Recovery model at 2pm - and the data owner at 4pm requests to recover the data to 3pm .   Based on the transaction log chain principles the database would only be able to be recovered up to the last transaction log backup before  the Recovery Model change at 2pm.


Microsoft lists various reasons for the transaction log breaking:

1.Switching the Recovery Modes from FULL to SIMPLE

2.Log Backups using the Truncate Only option.

3. Log Backup  with No_log Option  - Read more on BACKUP LOG WITH NO_LOG

4. Reverting database with Database Snapshot


It is possible to foresee how various ad-hoc actions could lead to  issues of not being able to support the agreed Recovery Point Objective (RPO). Many Enterprise data protection  systems have capabilities of assessing database transaction log chain and forcing Full backups  to start a new  transaction log chain and avoid RPO issues.      This automation is a useful procedure but should not be assumed.  


Read more 

Copy only backup in SQL server and not break the transaction log backup chain

What does LSN mean in SQL Server?

The mysterious case of Pseudo SIMPLE RECOVERY model and the transaction log explosive growth

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Reasons for Transaction Log Backup Chain breaking | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer