3

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?

Improve this question
3
  • 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, 2013 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, 2013 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, 2013 at 15:35

2 Answers 2

Reset to default
6

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.

Improve this answer
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.