Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
The error is very clear, you are not restoring the necessary log backups as they were created. You need to restore each after your full/diff in the correct order they were created.
LSN = Log Sequence Number.
Rather than confusing yourself with the actual LSN, look at the trn create date and follow the sequence and order when restoring.
Your backups must be restored in the following order
If you miss any of these out (aka restore a full then attempt to restore a transaction log without an applicable differential the LSN will not match and it will refuse to load it.
Reading the error it looks like you're trying to load a backup onto a installation which isn't up to date for that backup (aka you're missing a restore of either a full/differential/list of transaction logs).
The way backups are made mean that they require the previous backups to be processed (differential is all of the differences since the last full) transaction log (and tail log) is exactly what it says a log of the transactions, if any data is not present and exactly what it was when that transaction was applied then it will fail, so SQL simply wont allow it.
What is an LSN?
How can I determine which backup contains the correct LSN?
LSN, Log Sequence Number, is what uniquely identifies a log record in the transaction log, you can find more on this here: Introduction to Log Sequence Numbers
Every backup contains log records (yes, not only log backups, full and differential also, because the database restored from full or diff. backup should be consistent, you can find the details here: Understanding SQL Server Backups by Paul S. Randal)
So every backup has the inormation about itself (you can see it using RESTORE HEADERONLY) as well as msdb tables related to backups such as msdb..backupset and you can find there the information reguarding first LSN, last LSN, i.e. the min LSN and max LSN of log records that this backup contains.
It's very usefull information because knowing it makes it possible to relate every backup to other backups. The log backup chain as well as the corresponding full or diff backup is not determined based on backup_finish_date because there can be Copy-Only Backups, but all is determined in base of LSNs.
Different LSNs are recorded for every backup. The simplest are log backups LSNs: every successive log backup has the first LSN equals to the last LSN of previous one. For example, when you receive your error, this tells you that this backup has Fist LSN equals to 76120000013549400001, and in order to restore your backup chain you need to restore an earlier one (with Last LSN = 76120000013549400001)
The error tells you that you should find a log backup containing LSN = 75428000036693300001, this is because the last backup that you restored had the Last LSN = 75428000036693300001. All this information server get out of the last restored backup header, from its First LSN and Last LSN.
How can you find it?
The simplest way is using msdb..backupset, but if you are given the backup files only and has no access to the original server, you can gather all the information from your backup files in one table (create the table, insert into it exec('restore headeronly from disk = ...'))
I give you a code to get this information from msdb:
select top 1 b1.[type],
b1.first_lsn,
b1.last_lsn,
b3.physical_device_name
from msdb..backupset as b1
join msdb..backupmediaset as b2
on b1.media_set_id = b2.media_set_id
join msdb..backupmediafamily as b3
on b2.media_set_id = b3.media_set_id
where database_name = 'your_db' and (last_lsn) < 76120000013549400001
order by last_lsn desc;
This code will give you previous backup, but if you want to get the previous of previous, use top 2, ecc.
There are other usefull LSNs that every backup records, for example, every differential backup "knows" what full backup contains its differential base, the field DatabaseBackupLSN of differential points to CheckpointLSN of corresponding full backup. But this is another story. I've tried to write it short but this argument is wide, hope I answered your questions.
