Sql-server – How to choose which backup files to restore when identical timestamps exist for diff and log backups

backuprestoresql server

I can see from an earlier question that log file backup may be performed simultaneously with either full or differential data file backups.

Imagine for a moment that full backups run during a window of time that doesn't overlap with any other backup. However differential and log backups may overlap.

If, for example, this produces a differential backup at 11am on a given day, but also produces a log file backup at 11am on the same day, how do you decide which files to restore?

For example:

  • 01:00 AM = full backup
  • 10:00 AM = differential backup
  • 10:00 AM = log backup
  • 10:15 AM = log backup
  • 10:30 AM = log backup
  • 10:45 AM = log backup
  • 11:00 AM = differential backup
  • 11:00 AM = log backup
  • 11:05 AM = disaster

After restoring the full backup, do you restore the second differential backup and the last log backup? Or do you restore the first backup and all the log backups (even though the 10AM log backup really has the same issue as the 11AM backup – coinciding with the differential backup in each case).

I can see another question here for which some answers suggest third-party scripts to restore backup file headers in order to determine sequence numbers – do we really have to resort to header restores to determine which files are able to be restored in sequence?

Edit (19/12/18): I think my original question was worded poorly, so the answers it has received are varied. The key part I wanted to understand was actually mentioned by Jonathan in a comment on Sean's answer, and Jerry mentioned it too – that the restore sequence will handle the scenario if the log file contains information about data changes that have already been accounted for by the differential backup restore. (Sorry I didn't word the question well!) Sean's answer gives lots of additional info that is also helpful to understanding the restore sequence, so I can't pick an individual answer here, but I will upvote everything that helped with clarifications. Thanks.

Best Answer

After restoring the full backup, do you restore the second differential backup and the last log backup?

This is going to depend, but assuming:

  • The source server is offline and the data files cannot be accessed
  • The most up-to-date copy of the data is what is asked to be restored
  • All backup files are successfully accessible

Then you'd want to restore the 1:00 AM full backup, then the 11:00 AM differential, and there will most likely be some additional information in the 11:00 AM log backup depending on the length of time the differential and log backups took.

Or do you restore the first backup and all the log backups (even though the 10AM log backup really has the same issue as the 11AM backup - coinciding with the differential backup in each case).

I'm not sure what you mean about the 10:00 AM log backup having "the same issue" as the 11:00 AM log backup. There isn't an issue at all.

The whole point of a differential backup is to apply the changed extents and then keep just enough transactional information to make everything consistent. This greatly differs from a log backup which is the actual log records. They serve two different functions and you don't need differential backups in a restore to the latest point in time if you're in the full recovery model but you do need log backups to do so.

… do we really have to resort to header restores to determine which files are able to be restored in sequence?

You don't have to "resort to header restores" if:

  • You have an up-to-date copy of MSDB and can query the metadata out of it
  • Some other 3rd party application does the metadata shuffle for you
  • A human sits in front of a screen using SSMS to load up all the files and let the restore wizard figure it out by utilizing the backup header metadata
  • You keep some other metadata repository that is synchronized with your DR area

If, however, none of those (not complete, just examples) are the case then... yes, you'll need some way of figuring out which files go in which order. Since the easiest way to figure it out is to ask the backup what metadata it holds, that's what you'll find most people doing.

Related Question