This article explores the SQL Server Transaction log backups and log sequence number (LSN) in combination with the Full backups.
SQL Server Backup Introduction
The database backups are crucial for database recovery and disaster planning. It is the primary duty of a DBA to define the backup policy for each database based on the criticality, Recovery time object (RTO) and Recovery Point Objective (RPO). The database backups are useful even if you implemented the disaster recovery solutions like HADR SQL Server Always On. To meet these requirements, we schedule native or third-party backup tools to take database backups. We have the following database backups in SQL Server.
- Full backup: It is a complete database backup and allows to restore database till the time backup was completed. It is the most straightforward form of database backup
- Differential backup: It contains changes from the last full backup. These are cumulative backups
- Log backup: It takes SQL Server transaction log backup and contains data from the last log backup or first full database backup
We are not going to talk in detail about these backup types. You can refer to the article Understanding SQL Server Backup Types to gather details about them.
DBA combines these database backups to have a backup policy of a database. Usually, for large databases, we take a weekly full backup and the combination of differential and log backups in between. These database backups build a log chain, and it is very critical to maintain the log chain for database backups. We should also be aware of the actions that can break the log sequence. If the LSN chain is broken, it is difficult to restore the database, and in case of any disaster, if we cannot restore the database, it might create a problematic scenario for the DBA.
Suppose we have the following backup policy for a critical database.
- Weekly Full backup
- Daily differential backup
- Hourly SQL Server transaction log backup
In the above scenario, let’s say someone took a Full database backup after the SQL Server Transaction Log backup.
Questions
Now let me ask a few questions here:
- Will the full backup break the LSN chain?
- The transaction log backup after the full backup contains data from the full backup or not?
- If the database size is huge and full backup takes 4-5 hours to complete, what happens to hourly log backup?
- Would log backup work while the full backup is in progress?
If you know the answers to these questions, you can skip this article. I am sure most of the DBA would be confused and fail to answer these questions.
Overview of SQL Server backups and LSN
Let’s prepare the environment to explore answers to these questions.
Example 1: Full Database backup and LSN
Create a sample database and take a full database backup using the following query. This query takes the full backup, performs CHECKSUM and verify backup once finished.
|
1 2 3 4 5 6 7 |
BACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDB.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'SampleDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'SampleDB' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''SampleDB'' not found.', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'E:\DBbackup\SampleDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO |
Once backup is finished, execute the following query in database context for which we want to extract the details. It checks backup history for full, differential and SQL Server transaction log backups. It also gives the log sequence details for each backup types.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT s.database_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn, CAST(s.database_backup_lsn AS VARCHAR(50)) AS database_backup_lsn, CAST(s.checkpoint_lsn AS VARCHAR(50)) AS checkpoint_lsn, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name = DB_NAME() ORDER BY backup_start_date DESC, backup_finish_date GO |
In the output, we can look at the following values.
- First_lsn: It shows the log sequence number of the oldest log record
- Last_LSN: it shows the Last log sequence number in the backup set
- Checkpoint_LSN: it is the log sequence number of the last checkpoint
- Database_LSN: it shows the LSN of the last full database backup. In this case, we are taking a first full backup. Therefore, it shows the zero value
Example 2: Transaction log backup and LSN
Now open two new query windows.
Query Window 1: Execute the following query to take SQL Server Transaction Log backup at every 1-minute interval.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB1.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB2.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB3.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB4.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB5.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB6.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB7.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB8.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO |
Query Window 2: Execute the following query to generate transaction log activity every 30 seconds.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
INSERT INTO ExamResult VALUES ('Lily', 'Maths', 65 ); Go 100 WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResult VALUES ('Lily', 'Maths', 65 ); Go 100 WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResult VALUES ('Lily', 'Maths', 65 ); Go 100 WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResult VALUES ('Lily', 'Maths', 65 ); Go 100 WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResult VALUES ('Lily', 'Maths', 65 ); Go 100 WAITFOR DELAY '00:00:30.000' INSERT INTO ExamResult VALUES ('Lily', 'Maths', 65 ); Go 100 |
Once both the query gets completed, rerun the query to check the log backup history.
In this screenshot, you can note the following things.
- Database_backup_LSN for all log backup points to last full backup first_lsn
- For the first log backup, Last_lsn value corresponds to last_lsn of the full backup
- For all subsequent log backup, first_lsn is the last_lsn value of previous SQL Server Transaction Log backup
Example 3: Multiple full backups and subsequent log backup
Let’s take two full backup and subsequent SQL Server transaction log backups.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
BACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDB1.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM GO BACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDB2.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB9.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO WAITFOR DELAY '00:01:00.000' BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB10.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO |
Let’s view the database backup LSN information. In the following screenshot, we can note the following things.
- For the first full backup after the log backups, checkpoint_lsn and first_lsn values are same, and database_backup_lsn still point to first_lsn of the initial full backup
- For the next full backup also, checkpoint_lsn and first_lsn values are the same but the database_backup_lsn value changes to first_lsn of the previous full backup
- Log backup after the full backup has the first_LSN value equals to last_lsn of the last log backup. It shows that log backup does not break the LSN chain and it continues to maintain the chain since the last log backup
Example 4: Take SQL Server transaction log backup while a full backup is in running state
In the next step, let’s start log backup while the full backup is in running state. Open two new query window in SSMS.
Execute the following query to take full database backup in the first window.
|
1 2 |
BACKUP DATABASE [SampleDB] TO DISK = N'E:\DBbackup\SampleDBfull23.bak' WITH NOFORMAT, INIT, NAME = N'SampleDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM |
In the second query window, execute the query to take transaction log backup.
|
1 2 3 |
BACKUP LOG [SampleDB] TO DISK = N'E:\DBbackup\SampleDB23.trn' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO |
Once both the backups are finished, view the database backup history again.
Observe the following things from the query output.
- Full database backup started at 2019-07-16 13:26:20.000 and log backup started at 2019-07-16 13:26:21.000
- Log backup started before full database backup finished at 2019-07-16 13:26:28.000
- Both full and log backup show the similar database_backup_lsn because transaction log backup started before completion of the full backup, it references to old full backup
- The log backup LSN still matches with the last_LSN of previous transaction log backup. It shows that SQL Server transaction log backup maintains the log chain even if it is running while the full backup is in progress. Log truncation cannot occur during the full backup even if you are running the transaction log backup. It will occur with the first log backup after the full backup
Answers to initially asked questions
Let’s go back to questions asked initially and find out the answers.
-
Will the full backup break the LSN chain?
No, Full backup does not break the log sequence chain.
-
The transaction log backup after the full backup contains data from the full backup or not?
The transaction log backup takes data from the last LSN of previous log backup. It maintains the log chain; however, we can restore the full backup followed by the transaction log backup. SQL Server prepares a restoration plan as per the LSN during restore planning.
-
If the database size is enormous and full backup takes 4-5 hours to complete, what happens to hourly log backup?
Nothing, Log backup can continue to run as usual. The only difference is that it cannot truncate the transaction log due to in-progress full backup. Once we execute log backup after full backup completion, it truncates the log as well.
-
Would SQL Server transaction log backup work while the full backup is in progress?
As per the previous question, transaction log backup execution can work as usual. You will not face any failure in the transaction log backup job due to full backup progress.
Log sequence mismatch common reason
We can have many reasons that can break the log chain for database backup. If a particular database backup is corrupted or missing, it might impact the log chain and would make it difficult to restore the database.
Let’s explore the reasons that can break the log sequence.
- Sometimes DBA changes the recovery model to simple to execute bulk transactions that can lead to higher log growth. If we switch the recovery model from Full to Simple, it breaks the log sequence. Switching back the recovery model to FULL requires to set up a log chain again using the full backup and the subsequent SQL Server transaction log backups
- Switching recovery model from bulk-logged to simple also breaks the log sequence
- Suppose we take differential backup daily. The base for the differential backup is the last full backup. We should not take on-demand full backup in this case because it breaks the chain for the differential backup. Take full backup with the Copy_Only option in this case
- If we revert to a database snapshot, it also breaks the log sequence
Conclusion
In this article, we explored the concept of a SQL Server transaction log backup with LSN and how SQL Server maintains the chain with multiple scenarios. DBA should be aware of these scenarios to avoid any mistake that can break the log sequence. You should also perform the database restoration drills on a timely basis to test the recovery of databases from the backups.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023