0

I'm facing a scenario:

  1. Full backup of production database is created
  2. Full backup is restored to DR database
  3. A differential backup of production database is created, BUT accidentally deleted
  4. Start transaction log backup on production database

The question is, will the first generated transaction log would be able to be restored into DR database, or the deleted differential database backup must be restored to DR database first?

Brian Pham
  • 551
  • 9
  • 23

1 Answers1

1

The short answer is no, you need a/the differential backup or a new full backup. So either take a new full backup to restart the backup chain, or a new differential backup to replace the lost/deleted one.

Simplified, a Transaction Log backup will maintain a chronological list of changes made to your database since the last backup (Any backup; either full, differential or another transaction log backup). A differential backup will save a list of all extents that changed since the last Full Backup (so including whatever any transaction log backup already backed up in the mean time). This also means that if you create a full backup, followed by a differential backup, followed by a differential backup, the second differential backup contains everything the first one contains.

A typical scenario is where a full backup is taken, followed by a few transaction log backups. The next step then is a differential backup, and probably more transaction log backups and differential backups until a new full backup is created.

This ensures the time to recovery is reduced by ensuring that in case of disaster, you can take a backup of the tail of the transaction log. You can then restore the full backup, followed by the latest differential backup, followed by any transaction log backups (in chronological order).

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
  • Or else I have to generate another full database backup and re-restore it to DR database right? – Brian Pham Jul 06 '17 at 05:58
  • Either a new full, or a new differential backup. I updated my answer with a bunch of additional information. But yes, if the differential backup is gone, you either need a new full backup, or a new differential backup (as those simply backup all changed extents since the last full backup (including previous differential backups)). – SchmitzIT Jul 06 '17 at 06:01
  • Very clear & useful. Thanks a lot! I'll mark as answer – Brian Pham Jul 06 '17 at 06:06
  • @BogdanSahlean - That's why I changed the first line of my answer to "a/the differential backup". I will edit to make it more clear a new Differential backup would also solve the issue. – SchmitzIT Jul 06 '17 at 12:32
  • @BogdanSahlean You did see the word *simplified* in my answer, right? Besides, the question was whether the t-log created on the server AFTER the Differential backup was removed would be possible to restore – SchmitzIT Jul 07 '17 at 08:48
  • @BogdanSahlean The question does not mention any other t-log backups made, other than the one in step 4. Which cannot be restored. – SchmitzIT Jul 07 '17 at 08:58
  • @Bogdan because the of the wording in the question. 1. Full backup is created. 2. Full backup is restored. 3. Diff backup is created, but not restored. 4. T-log backup is created. - That t-log cannot be restored due to the missing backup from step 3. You mention it yourself in bullet 3; You can restore trns as long as the backyo chain isn't broken. Which it is, due to the missing diff backup. – SchmitzIT Jul 07 '17 at 09:07
  • @BogdanSahlean Maybe we are just mis-reading one another. IF the assumption is that OP saved all his T-log backups from the moment of the Full backup, then yes. However, I read the question as "Can I restore the T-log backup taken in step 4 as is, and be up and running?". – SchmitzIT Jul 07 '17 at 09:23
  • @BogdanSahlean That would assume that the sequence is always: Full -> Diff -> T-log. Normally, I would configure stuff like this: Full -> T-Log -> Diff ->T-log, etc. If in that case the diff is lost, but all T-logs are available, you're good. If you lose the diff and previous t-logs, you're in trouble. – SchmitzIT Jul 07 '17 at 09:53