Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
- sqlserver-dba.com switching from feedburner to follow.it
- Taking transaction log backup and full back at the same time
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 (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |