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

Question

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.

Answer 1

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.

Answer 2

I'd restore in below order first and hope 11am DIFF backup missed the disaster

01:00 AM = full backup
11:00 AM = differential backup

If DIFF backup actually took longer than 5 minutes and contained the disaster (or accidental DELETE), then you restore LOG backup instead up to 10:45am first Then, try 11am and see if it missed the disaster too

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 = log backup (optional)

I wouldn't worry about restoring overlapping DIFF/LOG as SQL will know which LSN to resume from, so either

10am DIFF + 10:15 LOG will work and continues    
or it'll want 10am DIFF + 10am LOG + 10:15am LOG and continues

Answer 3

RESTORE DATABASE DB_Name FROM DISK = ‘full_01_00.bak’ WITH NORECOVERY, RELPACE 

RESTORE DATABASE DB_Name FROM DISK = ‘diff_11_00.bak’ WITH NORECOVERY

Now, restore the logs:

RESTORE LOG DB_Name FROM DISK = 'log_10_45.bak' WITH NORECOVERY

 RESTORE LOG DB_Name FROM DISK = 'log_11_00.bak' WITH STOPAT = 'Dec 12, 2018 11:00 AM', RECOVERY