0

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.

4

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.

  • Thanks Sean for that overview. I'm sorry my question was confusing regarding the 10AM and 11AM backups having the same issue. What I mean is that at those timeslots you have both a DIFF and a LOG backup - how can we know in which order to restore those? If the DIFF completed first (or last), the answer is simple, but when they show the same time, should you restore the DIFF, then LOG (at risk the DIFF actually contained newer info), or vice versa? – youcantryreachingme Dec 10 '18 at 4:18
  • If you have the DIFF and the LOG there is a possibility that the log or diff may contain extra data. You'll need to look at the included LSNs in the backup metadata to understand which you should apply. There are various reasons why this could be and that's why you shouldn't go based on OS timestamps. @youcantryreachingme – Sean Gallardy Dec 10 '18 at 11:25
  • 1
    Even if the log doesn't have any information required for the restore, you won't invalidate the restore with an error. You will get an error about the log not containing any relevant extents. But the database is still there ready for more log files to be applied to it. In your situation I would just pick the full, the 11 AM diff, then the 11 AM log and then issue the recovery command. You may get an error with the 11 AM log, but it won't break anything or make you repeat steps. – Jonathan Fite Dec 10 '18 at 13:48
1

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
0
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

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.