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.

We are migrating a client to a new server. Their old server has SQL Server 2005. I have attempted to perform a differential backup however I received an error about the LSN chain being broken.

Please note I have done this many times, the full backup is restored on the new server with NORECOVERY and then when I go to restore the diff backup I get the error about the broken LSN chain.

I made a test database, got the same result. I then took the test database files and moved them to a server running SQL Server 2008 R2, the backup restored correctly.

Does anyone know why the differential backups from SQL Server 2005 will not restore properly on SQL Server 2012? And how can I fix this?

share|improve this question

migrated from stackoverflow.com May 2 '13 at 17:10

This question came from our site for professional and enthusiast programmers.

    
Did anyone take a full backup between the full backup that you restored and the differential backup that you tried (and failed) to restore? Check msdb.dbo.backuphistory. –  Ben Thul May 2 '13 at 17:25
4  
Somebody took a full backup (without specifying WITH COPY_ONLY) on the database since the full backup that you have onhand was taken. –  Thomas Stringer May 2 '13 at 18:10
    
Are you running the restore using t-sql or through the SSMS gui? I've seen this error when using SSMS 2012 - and then had the restore work fine when executed as a t-sql command. –  georgeb May 3 '13 at 15:35

2 Answers 2

There should be no issue restoring differentials and fulls from SQL 2005 to SQL 2012. I would validate that your backup files are compatible. To do this, you'll want to use the RESTORE HEADERONLY command and compare the full backup's FirstLSN value with the differential's DifferentialBaseLSN:

restore headeronly
from disk='X:\BackupFiles\foo.bak'

enter image description here

If these values do not match, then you will need to take an appropriate full backup.

share|improve this answer

Does anyone know why the differential backups from SQL Server 2005 will not restore properly on SQL Server 2012? And how can I fix this?

From BOL :

In SQL Server 2012, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version. However, backups of master, model and msdb that were created by using SQL Server 2005 or SQL Server 2008 cannot be restored by SQL Server 2012.

I have tried it and it works like mentioned in BOL.

As Thomas mentioned, Someone might have taken a full backup after you have taken a differential backup.

share|improve this answer

Your Answer

 
discard

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