Sql-server – Error when restoring transaction Logs

sql serversql-server-2012transaction-log

I'm trying to restore a database log, however I am getting this message:

Msg 4305, Level 16, State 1, Line 3
The log in this backup set begins at LSN 76120000013549400001, which is too recent to apply to the database. An earlier log backup that includes LSN 75428000036693300001 can be restored.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.

What is an LSN?

How can I determine which backup contains the correct LSN?

Best Answer

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:

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

Some general disclaimers, this script does not include any MOVE clauses or other customizations (such as restoring from a striped backup set). This won't be an issue for the TLog restores, but it may require that you adjust the Full (and potentially Differential) backup restore statement(s) to fit your needs. I use this often when setting up a new server as an AG replica as the file paths between servers should match up to make life easier.

Related Question