I was curious about how that particular problem is tackled.

1) Full backups performed on saturdays. 2) Differentials every other day. 3) Tlog backups every 15 minutes.

When trying to restore a database through SSMS: Right click on database > Restore...

It seems to hang for a long time then display the error message and display no backups to be restored and displaying the following error message:

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

Looking into it, I realize that we do have all the backups but I keep 1 month of backups.

I execute sp_delete_backuphistory weekly and delete anything older than 35 days to make sure that I don't remove history for valid backups.

There are situations where the history keeps track of backups which are no longer there and we get the error.

How did you guys handle that particular problem?

  • The thing is I never get to chose a backup file to restore from through ssms – Nicolas de Fontenay Dec 7 '15 at 17:29
  • So have you tried running the restore without the flaky wizard, e.g. using proper RESTORE commands? – Aaron Bertrand Dec 7 '15 at 17:30
  • Yes that actually works. I need to run a script which selects the max backupset id for a given database, then locate if there's a diff as well, then every subsequent tlog. Generate restore database commands for all of that. It's quite tedious. – Nicolas de Fontenay Dec 7 '15 at 17:33
  • So is sitting there waiting for a flaky wizard to spit an unhelpful error back at you. Automating the scripting of restores should not be that difficult and will be much more reliable. IMHO. – Aaron Bertrand Dec 7 '15 at 17:35
  • dba.stackexchange.com/questions/72854/… - This should have the droids you're looking for. Paul Brewer wrote a utility already that does the magic for you! I use it frequently and have restore scripts sent to my email address each day, just in case. – Kris Gruttemeyer Dec 7 '15 at 17:44