SQL Server日志恢复失败：日志备份LSN早于数据库备份的原因

操作脚本 #

主服务器备份脚本 #

DECLARE @backupFileName NVARCHAR(200);

SET @backupFileName = N'/var/opt/mssql/shipping/' + @databaseName + '.bak'
BACKUP DATABASE @databaseName TO DISK = @backupFileName

DECLARE @logFileName NVARCHAR(200);
SET @logFileName = N'/var/opt/mssql/shipping/' + @databaseName + '.trn'

BACKUP LOG @databaseName TO DISK = @logFileName

从服务器恢复脚本 #

DECLARE @restoreCommand NVARCHAR(200)
SET @restoreCommand  = 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' + @backupFileName + ''' WITH NORECOVERY'
EXECUTE ( @restoreCommand ) AT [172.21.23.37];

DECLARE @restoreLogCommand NVARCHAR(200)
SET @restoreLogCommand  = 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + @backupFileName + ''' WITH NORECOVERY'
EXECUTE ( @restoreLogCommand ) AT [172.21.23.37];

DECLARE @restoreFinalCommand NVARCHAR(200)
SET @restoreFinalCommand  = 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
EXECUTE ( @restoreFinalCommand ) AT [172.21.23.37];

执行日志与错误信息 #

备份及初始恢复成功日志 #

Processed 336 pages for database 'NewDB1', file 'NewDB1' on file 16.  
Processed 1 pages for database 'NewDB1', file 'NewDB1_log' on file 16.  
BACKUP DATABASE successfully processed 337 pages in 0.040 seconds (65.661 MB/sec).    
Processed 4 pages for database 'NewDB1', file 'NewDB1_log' on file 8.  
BACKUP LOG successfully processed 4 pages in 0.002 seconds (12.695 MB/sec).  
Processed 320 pages for database 'NewDB1', file 'NewDB1' on file 1.  
Processed 1 pages for database 'NewDB1', file 'NewDB1_log' on file  1.  
RESTORE DATABASE successfully processed 321 pages in 0.013 seconds (192.420 MB/sec).

错误提示 #

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Msg 4326, Level 16, State 1, Line 1

The log in this backup set terminates at LSN 40000000019400001, which is too early to apply to the database. A more recent log backup that includes LSN 40000000020000001 can be restored.

最终恢复日志 #

RESTORE DATABASE successfully processed 0 pages in 0.181 seconds (0.000 MB/sec).
Completion time: 2024-11-20T13:30:05.9994647+01:00

问题根源与解决方法 #

问题核心是恢复日志的脚本参数错误：执行RESTORE LOG时，错误引用了全量备份文件（.bak）而非日志备份文件（.trn）。

全量备份文件（.bak）包含的LSN是备份完成时刻的终点值（即错误提示里的LSN 40000000020000001），而你用这个文件去做日志恢复，系统会判断该备份的日志LSN早于当前数据库的LSN，因此抛出不匹配错误。

修正后的恢复脚本 #

DECLARE @restoreCommand NVARCHAR(200)
SET @restoreCommand  = 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' + @backupFileName + ''' WITH NORECOVERY'
EXECUTE ( @restoreCommand ) AT [172.21.23.37];

-- 替换为日志备份文件路径
DECLARE @restoreLogCommand NVARCHAR(200)
SET @restoreLogCommand  = 'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + @logFileName + ''' WITH NORECOVERY'
EXECUTE ( @restoreLogCommand ) AT [172.21.23.37];

DECLARE @restoreFinalCommand NVARCHAR(200)
SET @restoreFinalCommand  = 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
EXECUTE ( @restoreFinalCommand ) AT [172.21.23.37];

同时需额外确认：

• 从服务器可正常访问日志备份文件（.trn）的存储路径，权限配置正确
• 日志备份确实在全量备份之后执行，未被覆盖或替换

内容的提问来源于stack exchange，提问作者LDonSOvrfw