Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
You only need to restore the last differential file: a diff backup contains changes since last full backup. This is different to log backups which are incremental. What happens with the full+last diff only please? –  gbn Sep 21 '11 at 16:49
    
The last diff will not work since the lsn base is off. But thanks for the tip on only needing the last diff. –  slowpoh Sep 23 '11 at 21:49
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
This is exactly what I was looking for. I was able the confirm LSN was off and thus breaking the restore function. –  slowpoh Sep 23 '11 at 21:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.