Sql-server – Error restoring SQL differential database files

restoresql server

Occasionally I'm having problems restoring DB differentials. I'm getting the error "the differential backup cannot be restored because the database has not been restored to correct earlier state." From researching the issue, it appears that the particular differential I'm having an issue with is out of sequence.

Is there a way to check the database_backup_lsn from the backup file (.bak) I'm getting? I'm getting the full/differentials backup files from a vendor and it's not easily possible for me to query the source db and msdb…

When I try to restore the problematic differential file, I can see some lsn values in the SQL server management studio window. However, I can't seem to understand if I can figure out if the differential is out of sequence. I'm going to paste the lsn information below to see if someone else can figure it out from that info. I just may not be understanding how the lsn's work.

first lsn             last lsn            checkpoint lsn       full lsn            start date finish date
12642000000130300283 12642000000188400001 12642000000130300283 12642000000130300283 9/17/2011 23:53 9/17/2011 23:56
12742000000735900168 12742000000757500001 12742000000735900168 12742000000735900168 9/18/2011 23:24 9/18/2011 23:25
12951000012892000172 12951000012922000001 12951000012892000172 12951000012892000172 9/19/2011 23:27 9/19/2011 23:28
13123000002683100125 13123000003655500001 13123000002683100125 13123000002683100125 9/20/2011 23:26 9/20/2011 23:26

The first record is the full db and subsequent 3 are differentials. The full and the first 2 differentials restore fine. It's the final differential that is not restoring and showing the above message.

thanks.

Best Answer

To check the LSN number from the backup file do:

restore headeronly from disk='g:\backups\name_of_backup_file.bak'

The result set will have a column which will show you the last LSN number. As gbn said, to restore from a diff backup you need a full + last diff backup from the same backup set.