Sometimes when you restore your database backup you can get an “Unable to Create Restore Plan Due to Break in the LSN Chain” error. Let’s see how to deal with it.

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, all of a sudden, 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 the NORECOVERY option. After a successful operation, you restore the last differential backup file with the RECOVERY option but suddenly something goes wrong and you get the following error message:

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

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 your_database FROM DISK = 'full.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE your_database 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 the easiest way to restore your backups is to use the restore option in SQLBackupAndFTP. All you need is to create a restore job and click the “Run Now” button.

With SQLBackupAndFTP you can recover backups that have been performed via SQLBackupAndFTP, Standart T-SQL Command “CREATE BACKUP”, SQL script, or a .bacpac container, if the backups file name complies with the following format:

{DatabaseName}{YYYY}{MM}{DD}{hh}{mm}[log|diff].[sql|bacpac|7z|zip|bak]

Here is a short tutorial on how to Restore SQL Server Backups with SQLBackupAndFTP

Also, 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 the future.