Unable to create restore plan due to break in LSN chain

Question

My backup plan is:

1. Full backup - once a day
2. Differential backup - every four hours
3. Transaction Log backup - every 30 minutes.

All database backup are stored on my computer.

I add some wrong changes to my database and now I need to restore it to point-in-time (SQL Server 2012) with the help of SSMS.

Firstly I restored the last full backup WITH NORECOVERY, it restored successfully, then I tried to restore the last differential backup WITH NORECOVERY and received the following error message:

Unable to create restore plan due to break in LSN chain.

Please advise. What am I doing wrong?

Answer 1

Recently I have faced with the same issue. I have googled for a while and found that this is a problem in the Microsoft products. I wrote the article according to this error message, so you can find more information there.

Disclaimer: I am the Marketing Manager for Pranas.NET, maker of the Sql Backup and FTP tool promoted in that article.

So, to solve this issue and restore your database to point-in-time use T-SQL commands:

Restore your last full backup

RESTORE DATABASE your_database FROM DISK = 'd:/full' WITH NORECOVERY, REPLACE

Restore your last differential backup

RESTORE DATABASE your_database FROM DISK = 'd:/diff' WITH NORECOVERY

And restore your transaction log backups, when you will restore the last transaction log backup point the time you need to restore your database

RESTORE LOG your_database FROM DISK = 'd:/log1' WITH NORECOVERY

RESTORE LOG your_database FROM DISK = 'd:/log2' WITH STOPAT = '2016-01-05 13:29:59.000', RECOVERY

Answer 2

Which SP of SQL Server 2012 did you use? This is a known issue with SQL Server 2012 SP2.

The information for "Restore to:" is empty, which was supposed to show the timelines. To work around this issue:

1. Use SQL 2014 SSMS
2. Use T-SQL

Answer 3

It's because the SMSS GUI considers snapshots and copy_only backups as valid to restore from, when they are often not.

If you run your SQL Server within a Hyper-V VM, the VM backup will tell SQL to create a snapshot every night. I would imagine the same would apply to any other external backup solution.

You can run this query to hide the records from the backup history so SMSS can't see them:

UPDATE msdb.dbo.backupset 
SET database_name = database_name + '#' WHERE is_copy_only = 1 and database_name NOT LIKE '%#'
AND database_name NOT IN ('master', 'model', 'tempdb', 'msdb')

I've set this up on a SQL Agent job to run nightly after the hyper-v backup is done.

Answer 4

Adding up to the answer above, it is the issue that is resolved after SQL Server 2012 release. This restore issue is derived from the log files. I have seen some comments saying that "even though restore with UI doesn't work, restoring with query works well". However, the only working solution for me was to update SQL Server Management Studio. For example, updating the SSMS to the 2014 or higher versions genuinely resolves the issue.