Why can't I restore my SQL-Server database?

Question

I have a bunch of files: two backups and a list of transaction logs.

The backups are called:

• FB20241125_233033.bak
• FB20241126_233040.bak

(The filenames correspond with the date/time the backups have been taken.)

The transaction logs are called (commandline excerpt):

Prompt>dir *.trn
Directory of C:...\Transaction_Logs
25/11/2024  23:45         5.758.464 20241125224500.trn
26/11/2024  00:00         5.560.832 20241125230001.trn
26/11/2024  00:15         5.692.928 20241125231501.trn
...
26/11/2024  15:00         5.822.976 20241126140001.trn
26/11/2024  15:15         5.955.072 20241126141501.trn
26/11/2024  15:30         5.889.536 20241126143000.trn
27/11/2024  12:15         5.626.368 20241127111501.trn

(The same reason for filenames applies.)

Then I do a restore of this directory (take the two backup files and all the transaction log files), using the following properties:

This is the error message I get:

System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 33845000000619000001, which is too recent to apply to the database. An earlier log backup that includes LSN 33816000000750400001 can be restored. (Microsoft.SqlServer.SmoExtended)

I thought by myself "But that's obvious: my latest backup was taken at 23h30m40s on 26/11/2024 while the latest transaction log dates from the day later, so let's not include that transaction log in the restore.", but this doesn't help.

Does anybody know what I can do in order to restore the latest possible backup, containing the latest possible transaction logs?

Edit1 : what about LSNs in "*.trn" files?
In the meantime, I've run the following SQL query for the mentioned *.trn files:

RESTORE HEADERONLY FROM DISK = 'C:\...\20241125224500.trn';
RESTORE HEADERONLY FROM DISK = 'C:\...\20241125230001.trn';
...

These are the first results:

FirstLSN               LastLSN             
---------------------- --------------------
33758000001254200001   33759000000515900001
33759000000515900001   33759000001579300001
33759000001579300001   33760000000835100001
33760000000835100001   33761000000090800001
33761000000090800001   33761000001176400001
33761000001176400001   33762000000412500001
33762000000412500001   33762000001492100001
33762000001492100001   33763000000648100001
33763000000648100001   33763000001722200001

As you see, there are quite come *.trn files, having 3375... and 3376... as first Log Sequence Number (LSN), so why is the error message saying that the log in this backup set begins at LSN 3384...?

Thanks in advance

Answer 1

All that the GUI does is to create a bunch of T-SQL commands for you. The restore dialog, in particular is a very difficult beast to understand. When I teach, I can explain backup and restore including the commands in about 30 minutes, and then I spend 3 hours explaining what the nuances in this dialog does.

I.e., it is a good idea to be able to work independently of the GUI, when it doesn't do what you want, the complexity of the GUI is too high, it is incomprehensible, or when there are nasty bugs in the GUI.

So, you can use the Script button to script the RESTORE commands generated by SSMS, and work those scripted RESTORE commands to match what files you have and which of those you want to restore.

In my example, and the way I used the restore dialog, the scripted commands came out as below. (Note that SSMS adds some junk to the backup command which is irrelevant, no-ops and shouldn't be there. But that it unlikely to change.)

USE [master]
BACKUP LOG [Olf] TO  DISK = N'R:\SqlBackups\Olf_LogBackup_2024-11-29_18-30-16.bak' WITH NOFORMAT, NOINIT,  NAME = N'Olf_LogBackup_2024-11-29_18-30-16', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [Olf] FROM  DISK = N'R:\Olf.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE DATABASE [Olf] FROM  DISK = N'R:\Olf.bak' WITH  FILE = 7,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Olf] FROM  DISK = N'R:\Olf.bak' WITH  FILE = 8,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [Olf] FROM  DISK = N'R:\Olf.bak' WITH  FILE = 9,  NOUNLOAD,  STATS = 5

Hopefullt you aren't lacking a log backup file "in between", since the one before the missing one would be the last one you can restore.

Answer 2

I think this query here from my friend John can help you:

/*-------------------------------------------------------------------------*\
    This query will generate the proper restore chain for all databases hosted on the instance. 
    This should provide you with the proper statements and in their proper order. 
    Just copy the statements out of the RestoreStatement field and paste them into a new query window:
\*--------------------------------------------------------------------------*/
-- John Eisbrener
-- https://dba.stackexchange.com/a/176175/22336
WITH BackupHist
AS
(
       SELECT
                s.server_name
              , d.name AS database_name
              , m.physical_device_name
              , CASE m.device_type
                     WHEN 2 THEN 'Disk'
                     WHEN 102 THEN 'Backup Device (Disk)'
                     WHEN 5 THEN 'Tape'
                     WHEN 105 THEN 'Backup Device (Tape)'
                     WHEN 7 THEN 'Virtual Device'
                END AS device_type
              , CAST (s.backup_size / 1048576.0 AS FLOAT) AS backup_size_mb
              , CAST (s.compressed_backup_size / 1048576.0 AS FLOAT) AS compressed_backup_size_mb
              , s.backup_start_date
              , s.first_lsn
              , s.backup_finish_date
              , s.database_backup_lsn
              , CASE s.[type]
                     WHEN 'D' THEN 'Database (Full)'
                     WHEN 'I' THEN 'Database (Differential)'
                     WHEN 'L' THEN 'Transaction Log'
                     WHEN 'F' THEN 'File or Filegroup (Full)'
                     WHEN 'G' THEN 'File or Filegroup (DIfferential)'
                     WHEN 'P' THEN 'Partial (Full)'
                     WHEN 'Q' THEN 'Partial (Differential)'
                END AS backup_type
              , s.recovery_model
              , ROW_NUMBER () OVER (PARTITION BY s.database_name, 
                                    s.[type],
                                    s.database_backup_lsn 
                                    ORDER BY s.backup_start_date) AS Row
       FROM msdb.dbo.backupset s 
       INNER JOIN msdb.dbo.backupmediafamily m
              ON s.media_set_id = m.media_set_id
       RIGHT OUTER JOIN sys.databases d
              ON s.database_name = d.name
              AND s.recovery_model = d.recovery_model_desc
       COLLATE SQL_Latin1_General_CP1_CI_AS
), BackupHistFullIterations AS
(
    SELECT database_name
         , backup_finish_date
         , first_lsn
         , ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM BackupHist
    WHERE backup_type = 'Database (Full)'
)
SELECT    bh.server_name
        , bh.database_name
        , bh.backup_finish_date
        , bh.backup_type
        , CASE backup_type WHEN 'Database (Full)' THEN 'RESTORE DATABASE [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
            WHEN 'Transaction Log' THEN 'RESTORE LOG [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
            ELSE ''
          END AS RestoreStatement
FROM BackupHist bh INNER JOIN 
        (
            SELECT *
            FROM BackupHistFullIterations
            WHERE BackupIteration = 1 -- Show the X most recent iteration(s)
        ) bhfi
     ON bh.database_name = bhfi.database_name
    AND (bh.database_backup_lsn >= bhfi.first_lsn
         OR bh.first_lsn = bhfi.first_lsn)
    AND (bh.backup_finish_date >= bhfi.backup_finish_date)  -- used in case db was rebuilt/lsn reset
ORDER BY 1, 2, 3