Sql-server – The CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backupbackup true or false

sql server

I have a question that related to SQL Server Checkpoint Log Sequence Number.

I've read some articles and they say that the CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup, but I can't find the confirmation on the https://technet.microsoft.com page.

I have a doubt about it because I read that the only possible way to anchor LOG backup to DIFF is: DIFF.DatabaseBackupLSN =< LOG.DatabaseBackupLSN, DIFF.LastLSN < LOG.LastLSN

Also, I have made the following test and it seems that the CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup. enter image description here

So is it correct?

Best Answer

No it does not map all the time. It depends on the interval of checkpoint happening. You can test this with the code below.

To explain what I said in my comment you can test with this code.

Use the code from here to load 300 rows.

Create a new table.

USE TestChkPoint
GO

CREATE TABLE dbo.TestTableSize
(
    MyKeyField VARCHAR(10) NOT NULL,
    MyDate1 DATETIME NOT NULL,
    MyDate2 DATETIME NOT NULL,
    MyDate3 DATETIME NOT NULL,
    MyDate4 DATETIME NOT NULL,
    MyDate5 DATETIME NOT NULL
)
GO

You will see different CheckpointLSN between diff and log1 backup if you perform below action.

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--insert new data
CHECKPOINT 
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'

You will see same checkpointLSN in you diff and log1 backup if you perform this actions. Notice I commented out manual checkpoint.

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--insert new data
--CHECKPOINT 
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'

Now if you set TARGET_RECOVERY_TIME to 1 second and perform below steps without manual checkpoint you will see different checkpointLSN between diff and log backup.

ALTER DATABASE TestChkPoint SET TARGET_RECOVERY_TIME = 1 SECONDS;  

--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.Full.bak'
--insert new data
BACKUP DATABASE TestChkPoint TO DISK= 'C:\TestChkPoint.diff.bak' WITH DIFFERENTIAL
--no manual checkpoint
--insert new data change 300 to 30000
BACKUP LOG TestChkPoint TO DISK= 'C:\TestChkPoint.log1.bak'
Related Question