My backup plan is:
- Full backup – once a day
- Differential backup – every four hours
- 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?
Best Answer
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