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.