5

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?

  • 1
    when you run this: select top 100 [name], [backup_start_date], [type], [first_lsn], [database_backup_lsn], [database_name] from msdb.dbo.backupset where [database_name] like '...' order by [backup_start_date] desc Do you see any type D line between your full (type D) and Diff (type I)? – Julien Vavasseur Feb 4 '16 at 13:56
  • 3
    This means their was full backup taken after the full backup you are trying to restore. Diff backups links itself to previous full backup and you did not restored that full backup. May be some other third party backup happening. Use msdb.dbo.backupset to find this information – Shanky Feb 4 '16 at 14:03
3

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

  • 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
  • 1
    @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 – Alexandr Omelchenko Feb 5 '16 at 13:28
  • 1
    To sum up: Quit using the GUI to do restores. Also, apply the latest service pack. – Anti-weakpasswords 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.

Your Answer

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