If you would like to participate in this month’s blog party, go to Kenneth’s invitational blog post: T-SQL Tuesday #85: Backup and Recovery. While reading some of the other posts participating in this month’s event, I noticed a statement saying that a differential backup can only be restored after the full backup it is based on. This is mostly accurate, but there is more to it than that.
All About the LSNs
Recovery is all about the LSNs or Log Sequence Numbers. Ever log record is assigned an LSN and when doing restores, you have to make sure there are no gaps in the LSN chain to be able to do a successful restore. When we talk about restoring the last full backup plus the last differential backup plus all log backups since then, we are really defining the shortest path to get from the start of the LSN chain to the end of it. There are many paths you could take to get there. Most of these paths will take longer, so who really cares about taking the longer path?
When this really comes in to play is when one of those backup are missing or corrupted. Differential and log backups are excellent ways to span gaps in the LSN chain as long as they cover the missing LSNs. Case in point, if I decide to seed a log shipping secondary with a full backup and I get distracted from my task by a production issue and by the time I get around to creating a differential backup, another full backup runs, I don’t have to restore that newer full backup to be able to restore the differential I’m about to take. If I have all of the log backups between the previous full backup and the new one, I can use those log backups to span the LSN gap.
First, I will take a series of backups: Full #1 -> Log #1 -> Log #2 -> Full #2 -> Log #3 -> Differential #1
Conventional knowledge says I shouldn’t be able to restore Differential #1 without first restoring Full #2. Let’s test it out:
Use master; -- Create a new database Create Database TestRestores; Go -- Make sure it is using the full recovery model Alter Database TestRestores Set Recovery Full; Go -- Take a full backup Backup Database TestRestores To Disk = 'c:\bak\TestRestores_Full1.bak' With init; -- Switch to the database Use TestRestores; -- Add some data Select * Into dbo.AllDBs From sys.databases; --Take a log backup -- Log chain intiialized Backup Log TestRestores To Disk = 'c:\bak\TestRestores_Log1.trn' With init; -- Add some more data Select * Into dbo.AllDBs2 From sys.databases; --Take another log backup Backup Log TestRestores To Disk = 'c:\bak\TestRestores_Log2.trn' With init; -- Take another full backup Backup Database TestRestores To Disk = 'c:\bak\TestRestores_Full2.bak' With init; -- Add some more data Select * Into dbo.AllDBs3 From sys.databases; --Take another log backup Backup Log TestRestores To Disk = 'c:\bak\TestRestores_Log3.trn' With init; -- Add some more data Select * Into dbo.AllDBs4 From sys.databases; -- Take a differential backup Backup Database TestRestores To Disk = 'c:\bak\TestRestores_Diff1.bak' With differential, init; Go
Now to test restoring the differential. My successful restore, if I am not mistaken, should be: Full #1 -> Log #1 -> Log #2 -> Log #3 -> Differential #1
First, I will drop the existing database before we restore it.
-- Drop the database and then restore it -- Switch to master database Use master; -- Drop database Drop Database TestRestores; Go
Now I will restore full backup #1 and then see what happens if I try to restore the differential.
-- Restore Full1 with NoRecovery to allow further backups to be restored Restore Database TestRestores From Disk = 'c:\bak\TestRestores_Full1.bak' With NoRecovery; Go -- Test trying to restore Diff1 backup -- will fail because it is based off of Full2 Restore Database TestRestores From Disk = 'c:\bak\TestRestores_Diff1.bak' With NoRecovery;
I got the following error indicating that there is an LSN gap between last LSN of full backup #1 and differential #1.
Msg 3136, Level 16, State 1, Line 80
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 80
RESTORE DATABASE is terminating abnormally.
Now, I proceed with restoring the log backups to bridge the LSN gap between Full #1 and Full #2. This will bring the LSN beyond the last LSN of Full #2, but that’s okay. SQL Server knows how to handle that.
-- Restore log backups to span LSN gap between Full1 and Full2 Restore Log TestRestores From Disk = 'c:\bak\TestRestores_Log1.trn' With NoRecovery; Restore Log TestRestores From Disk = 'c:\bak\TestRestores_Log2.trn' With NoRecovery; Restore Log TestRestores From Disk = 'c:\bak\TestRestores_Log3.trn' With NoRecovery; -- Test trying to restore Diff1 backup with Recovery to bring database online -- will be successful because the log backups spanned the LSN gap Restore Database TestRestores From Disk = 'c:\bak\TestRestores_Diff1.bak' With Recovery; Go
All restores are successful at this point, and I cna now query to see if the data in dbo.AllDBs4 which was created and populated after log #3 and before differential #1 is there, and it is.
Select * From TestRestores.dbo.AllDBs4;
So remember, if you ever find yourself in an irregular situation, ask yourself how you can bridge the missing LSN gap with backups you have on hand or can create. There’s many ways to restore and recover a database. It’s all about the LSN chain.