Sometimes when you restore your database backup you can get:
“Unable to Create Restore Plan Due to Break in the LSN Chain” error.:
Let’s assume that you have to administer a database with the help of Microsoft SQL Server Management Studio (SSMS). All backups are made as scheduled and everything works just fine, and then, suddenly, a failure occurs and now you need to restore your database. You have all backups that you need to restore your database, so you start the restoration process from the last full backup with NORECOVERY option. After a successful operation you restore last differential backup file with the RECOVERY option but suddenly something goes wrong and you get the following error message:
This error occurs due to a problem with Microsoft products, and can occur in SQL Server Management Objects and only when you use Microsoft SQL Server Management Studio for database recovery.
To solve this issue you need to restore the database manually using the following T-SQL commands to perform full and differential backups:
RESTORE DATABASE BILLING FROM DISK = 'full.bak' WITH NORECOVERY,
REPLACE
RESTORE DATABASE BILLING FROM DISK = 'diff.bak' WITH RECOVERY
If you prefer to continue working with Microsoft SQL Server Management Studio you have to restore backups not one by one but all at once.
But if you need to restore your full backup quickly, then use One-Click SQL Restore. It is a simple and free tool that restores full MS SQL server database backups.
Note: Microsoft recommends installing the latest cumulative update for SQL Server to avoid getting “unable to create restore plan due to break in the lsn chain” error message in future.
My backup plan is:
1. Full backup - once a day
2. Differential backup - every four hours
3. Transaction Log backup - every 30 minutes
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
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
