4

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?

6
  • Tables got deleted today at 8 AM. The DB was in FULL recovery model at all time, so logs are present. They just where not backed up to a .trn file regulary. But the full-backup was taken everyday. So i have a FULL-Backup from yesterday and a single .trn file which was generated manually this morning and contains several days of logs. I have no differential.
    – MHeld
    May 8 '19 at 12:51
  • What time this log backup was taken? As per your question, this is after 8AM? May 8 '19 at 12:53
  • Yes, it was taken after 8PM - so it was taken after the accidential deletion.
    – MHeld
    May 8 '19 at 12:54
  • If you check the error message, there is mismatch in the Page:LSN and PrevPageLSN, In order to have successful log backup restoration, these two needs to be same. Did you try to restore to a previous time say 7:30AM? May 8 '19 at 13:12
  • Yes i tried several times until close to the full backup. No success.
    – MHeld
    May 8 '19 at 13:14
3

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.

0

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.