Sql-server – How to restore to a point in time between the last log backup before a full backup and the full backup

restoresql server

Imagine the following timeline:

  • T0 = full backup
  • T1 = log backup
  • T2 = log backup
  • T3 = log backup
  • T4 = full backup
  • T5 = log backup

After all the above have transpired, if you still have all the backup files, how would you restore to a point in time between T3 and T4?

My understanding is that the full backup at T4 should lead the log file to mark transactions between T0 and T4 as inactive, so in theory, the transaction log may no longer contain transactions from the time before T4. In particular, the log backup at T5 will only contain transaction information from the time after T4. Is this correct?

Best Answer

how would you restore to a point in time between T3 and T4?

You should restore this way:

  • T0 = full backup
  • T1 = log backup
  • T2 = log backup
  • T3 = log backup
  • T5 = log backup

I did not understand what you said here:

My understanding is that the full backup at T4 should lead the log file to mark transactions between T0 and T4 as inactive, so in theory, the transaction log may no longer contain transactions from the time before T4.

But this one:

the log backup at T5 will only contain transaction information from the time after T4.

is INCORRECT.

Every log backup starts from LSN where previous log backup stops, so it doesn't matter if there was or was not any full backup between T3 and T5.

You could take a "shortcut" by restoring only T4 and T5 in case the time you are interested in was after T4, but it's not your case, so you should restore all the log backups until the last one that covers the time you are interested in, starting from full backup that precedes the time you are interested.

P.S. May be it can be useful to you to know that the only thing that can "truncate" the log in full recovery model is log backup. Full backup has nothing to do with "marking transactions inactive".

This article A SQL Server DBA myth a day: (30/30) backup myths may also help.

Related Question