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..."?

Question

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 Full backup and only loading the Transaction Log backups to restore. I really think there's a broken backup chain of sorts that is confusing SSMS:

If I only select my Full backup to restore, then it lets me click the script button and this is the script for just the Full backup by itself:

USE [master]
RESTORE DATABASE [MyDatabase] FROM  DISK = N'P:\MyDatabase\MyDatabase_backup_2021_09_16_223001_3540051.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

Additionally my maintenance plan is not set for "Copy-only backups":

For reference, here is the T-SQL script for one of the databases in the Full backup maintenance plan:

BACKUP DATABASE [master] TO  DISK = N'\\OurDomain\Shares\SQL Backups\Full Backups\master\master_backup_2021_09_19_004931_6111907.bak' WITH  RETAINDAYS = 14, NOFORMAT, NOINIT,  NAME = N'master_backup_2021_09_19_004931_6111907', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

If I manually script out a restore of my Full backup and the most closest Transaction Log backup that occurred after that Full backup, it restores the Full backup fine and leaves the database in NORECOVERY but the Transaction Log backup fails with the following error:

These are the manual scripts I wrote:

USE [master]

RESTORE DATABASE [MyDatabase] 
FROM DISK = N'P:\MyDatabase\MyDatabase_backup_2021_09_16_223001_3540051.bak'
WITH NORECOVERY;

RESTORE LOG [MyDatabase]
FROM DISK = N'P:\MyDatabase\MyDatabase_backup_2021_09_16_223001_4008446.trn'
WITH RECOVERY;

Oddly if I try restoring the closest Transaction Log backup that occurred just before the Full backup, I get the same exact error message referencing exactly the same LSNs. So it's like my Transaction Log backups are not following the same backup chain as my Full backup?

The only other relevant information I can think of, is these backup maintenance plans were setup originally on a SQL Server 2016 instance. The server of that instance crashed, and we spun up a brand new server with a SQL Server 2019 instance. I restored all the user and system databases from backups (ironically) to the new SQL Server 2019 instance. (The system databases I had to first restore on a 2016 instance, and do an in-place upgrade before it allowed me to restore them on the new 2019 server.) The only database I couldn't restore to the new server successfully was the master database. Everything else seems to be running fine so far.

Answer 1

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"...

Answer 2

Our server level backup software, Veeam, was apparently configured to truncate the Transaction Log. I had no knowledge that Veeam even could interact at the database level until the wise Tibor Karaszi pointed me in the right direction:

(This was configured long before my existence. Side note, I see absolutely no reason why Veeam would even offer this terrible option.)

Changing this to backup the logs themselves (instead of having a maintenance plan do it) would fix the issue. But I prefer letting SQL Server natively manage the backups, so we just disabled the management of this in the Veeam Backup & Replication software.

In the Veeam Backup & Replication software, under the Edit Backup Job window, in the Guest Processing screen, we unchecked "Enable application-aware processing" to disable it.

For reference, this is Veeam's documentation on configuring it to manage the Transaction Log.