Why does a Full Backup restore of my database give me the error "RESTORE can only create a database when restoring either a full backup..."?

I've setup a maintenance plan to take a daily Full backup of all databases on my server. I've also setup a second maintenance plan to take Transaction Log backups every 5 minutes. The Transaction Log backups job stops and restarts between 11 PM and 1 AM, to allow server level backups to be taken (at the advisement of the Infrastructure team, perhaps to be revisited). I don't believe this should be an issue though, as I have another server following the same pattern and has no issues with its backups.

I am trying to verify my backups are working properly but any time I try to restore the Full backup with any of the Transaction Log backups that came after it, I immediately get the following error:

I'm trying to restore the backups onto a separate server (same SQL Server version) whose primary purpose right now is testing the database backup integrity. The database I'm trying to restore currently does not exist on that server.

If it's of any relevancy, these are the Restore Options I'm using:

Following this interesting answer by Aaron Bertrand to a similar question, I'm now investigating a few things with the RESTORE HEADERONLY FROM DISK command. Firstly, these are the results for my Full backup file:

Only one database / file stored in that Full backup.

Additionally, if I run RESTORE HEADERONLY FROM DISK for my first Transaction Log backup file after the last Full backup, for the Full backup file itself, and the last Transaction Log backup file before the last Full backup I notice the following LSNs:

I thought the DatabaseBackupLSN of all of the backups are supposed to match the FirstLSN of the last Full backup prior to those backups. It's odd to me that they all seem to point to an older Full Backup.

When I try to generate the scripts of the restore that SSMS is trying to do, oddly I get the same error as a tooltip, and it won't give me the scripts:

Upon closer inspection, when I select all my backups including the Full, SSMS is automatically removing

The bottom line is that you (unfortunately) can't rely on SSMS to help you with restore. Because it doesn't do it right. I have a case I reported 2014 which IMO is bad where SSMS tries to base a restore sequence on a copy_only backup, and I have reminded MS on this a few times. This has fallen on deaf ears, so that is a pretty clear message to me. See this for a couple of examples: http://sqlblog.karaszi.com/?s=restore.

In your case, we don't have enough to repro. And without a repro, there's nothing we can do except say something like "seems to be a bug in SSMS". We don't know what backup you have performed, we don't know what you click in the GUI, we don't know what backup files you have on the second server. Etc.

If you want us to test whether you misunderstand the GUI, give us a repro.

• All T-SQL commands to begin with, where you produce the backups.

• A minimalistic repro and a clear description exactly of what files you have.

• The TSQL restore commands that you expect the GUI to produce. Make sure that they work as you expect.

• And exactly how you click the GUI so we can repro your case.

Perhaps a tall order for you but without that we are, as I mentioned, left with "seems to be a bug in SSMS"...

StackExchange Database Administrators Q#299788, answer score: 2