Sql-server – Point in time Restore failed – Error 3456 ‘Could not redo log record’

restoresql server

I have a database from which all tables got deleted by accident. Unfortunately we do not have a 'perfect' backup and now my problem comes in:

  • FULL Backup is from yesterday, 18:00
  • LOG Backup wasn't taken for several days. That backup was performed manually after the delete.
  • Both files are available to me to perform a point-in-time restore

To restore point-in-time, i restored the FULL-Backup WITH NORECOVERY. Then i tried to apply the Log-Backup. After it reaches 100% in GUI, i get the following error:

Error

LSN-Information of FULL-Backup:

LSN FULL

LSN-Information of LOG-Backup:
LSN Log

Am i right that the overlapping LSNs of FULL and LOG-Backup are the problem here? I think it is not accidential that PrevPageLSN from the error matches the FirstLSN of the FULL backup. Ideas how to get the database restored point-in-time?

Best Answer

From the docs:

As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target restore time.

Do you have an older full backup? Contrary to popular belief, full backups do not break the "log backup chain." So you should be able to restore an older full backup (that meets this requirement, and then do point in time recovery from the log backup that you took after the delete.

Another possible issue that comes to mind: maybe you have log backups running that you don't know about. This seems unlikely at this point, but you could check for that by querying msdb.dbo.backupset:

USE [msdb];
GO

SELECT TOP (100) 
    [name],
    first_lsn,
    last_lsn,
    checkpoint_lsn,
    backup_start_date,
    backup_finish_date,
    [type],
    [database_name]
FROM dbo.backupset
WHERE [database_name] = 'hydra1'
ORDER BY backup_start_date DESC;

If there any backups with type = 'L' after the full backup other than the one you created, then you'll need to restore those as well.

Since you've confirmed there is no corruption present in the FULL backup, it's possible that there is corruption in the LOG backup you're trying to use. You could try running CHECKDB on the live database to see of the current log is corrupted.

Related Question