4

I'm trying to restore a full database backup plus transaction logs to the most recent transaction log. I've set up transaction logs to ship out every 15 minutes. This setup was performed via the SMSS GUI. The full database backup starts at 12 AM daily, and usually finishes before 2 AM.

First, I restore the full backup with NORECOVERY.:

USE [master]
RESTORE DATABASE [DBNAME] FROM  DISK = N'X:\path\to\DBNAME.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

This completes successfully.

Then, I grab the oldest transaction log and run the following:

RESTORE LOG DBNAME FROM DISK = 'X:\path\to\OldestTransactionLog.trn' WITH NORECOVERY;

However, I get the following error:

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

I don't understand why this is too recent!

My backup takes place at 12 AM and finishes by 2 AM.

At first I tried the transaction log from 12 AM, then seeing the "too recent" message, tried the oldest one I have, from 11:30 AM yesterday.

So this transaction log should be half a day older than the most recent full database backup.

Am I missing something here? How can this be too recent?

Any help is appreciated - and this is not on a production system, don't worry!

  • 4
    You can only restore log backups taken after the full backup. You must restore the log backups in exactly the same order they were taken, without missing any, up to the point in time you desire. The last restore must be done using "with recovery" if you want to access the database afterwards. – Max Vernon May 21 '15 at 17:53
  • @MaxVernon Nope. If the log was too early, the error message would say it was too early, not too recent. – Roger Willcocks Oct 23 '15 at 8:59
  • I think you're missing the point of this error. It states there is an intermediary log that needs to be restored prior to the one you're attempting to restore can be replayed against the database. – Max Vernon Oct 23 '15 at 11:44
2

The last LSN of the FULL BACKUP should match the Last LSN of the log backup. This will be your first log backup and then after that the consecutive log backups will be having A.LAST_LSN = B.FIRST_LSN --> where B = log backup taken immediately after A.

From BOL :

enter image description here

Now look at the error you are getting :

The log in this backup set begins at LSN 421814812000000025600001, which is too recent to apply to the database. An earlier log backup that includes LSN 421787067000000013800001 can be restored.

So, essentially, you have a gap in the LSN and hence you cannot restore your log backup.

I would suggest to use Restore Gene - to automate DB restores. You can even use it using PowerShell to fully automate your restores. (I have used it and its very useful.)

Refer to my answer to better understand how full backup and LSN works.

2

You are trying to apply a Log file that finish with LSN 421814812000000025600001 into a DB restored with LSN 421787067000000013800001 , so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file would not possible to apply , being a change in sequence.

So possible cause here being that you are not restoring transaction log backups in order :

Also, you can use the below script to determine for restoring the available backups in order:as mentioned here

    USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 

-- 2 - Initialize variables 
SET @dbName = 'Customer' 
SET @backupPath = 'D:\SQLBackups\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE' 
PRINT @cmd 

-- 4 - Find latest diff backup 
SELECT @lastDiffBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.DIF'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

-- check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY' 
   PRINT @cmd 
   SET @lastFullBackup = @lastDiffBackup 
END 

-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR  
   SELECT backupFile  
   FROM @fileList 
   WHERE backupFile LIKE '%.TRN'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

OPEN backupFiles  

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @backupFile + ''' WITH NORECOVERY' 
   PRINT @cmd 
   FETCH NEXT FROM backupFiles INTO @backupFile  
END 

CLOSE backupFiles  
DEALLOCATE backupFiles  

-- 6 - put database in a useable state 
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY' 
PRINT @cmd 
1

When trying to restore from SMSS I found that it skipped my first t-log. I modified the statement to include the missing transaction and had no issue. Maybe this occurs due to overlapping execution of full backup and hourly log (uneducated guess)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.