7

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.

enter image description here

Please advise. What am I doing wrong?

0
4

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

3
  • can you explain what exactly this problem with the Microsoft products? In my experience the problem is always that there was a mysterious backup being taken, usually by a scheduled process in a VM environment.
    – datagod
    Feb 5 '16 at 13:17
  • 2
    @datagod Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section - support.microsoft.com/en-us/kb/3065060 Feb 5 '16 at 13:28
  • 1
    To sum up: Quit using the GUI to do restores. Also, apply the latest service pack. Feb 8 '16 at 0:31
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
1

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.

-1

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.

1
  • 1
    Please add further details to expand on your answer, such as working code or documentation citations.
    – Community Bot
    Sep 1 '21 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy