Database Recovery Analysis: Dealing with a Missing Full Backup

The Backup Scenario

Let’s first summarize the backup schedule that was in place:

Monday:

• 2 PM: Full backup
• 5 PM: Differential backup
• 7 PM: Transaction log (T-log) backup
• 8 PM: T-log backup

Tuesday:

• 2 PM: Full backup (deleted)
• 5 PM: Differential backup
• 7 PM: T-log backup
• 8 PM: T-log backup

The database became corrupted at 8:01 PM on Tuesday, and we’ve discovered that Tuesday’s 2 PM full backup is missing.

Understanding Backup Types

Before answering the questions, let’s review how these backup types work together:

1. Full backup: Contains all the data in the database at the time of the backup.
2. Differential backup: Contains only the data that has changed since the last full backup.
3. Transaction log backup: Contains all transaction log records since the last log backup (or since the last full backup if it’s the first log backup after a full backup).

Question 1: Up to what day/time can we restore from available backups?

Answer: We can restore up to Monday at 8 PM.

Why?

Here’s the recovery path we can follow:

1. Restore Monday’s 2 PM full backup: This is our most recent intact full backup.
2. Restore Monday’s 5 PM differential backup: This contains all changes since Monday’s full backup.
3. Restore Monday’s 7 PM T-log backup: Contains transactions between 5 PM and 7 PM.
4. Restore Monday’s 8 PM T-log backup: Contains transactions between 7 PM and 8 PM.

We cannot use Tuesday’s differential or T-log backups because:

• Differential backups depend on their base full backup (Tuesday’s 2 PM, which is missing)
• T-log backups after the missing full backup cannot be applied because we don’t have the starting point they build upon

Question 2: Can we restore the database up to Tuesday 8 PM?

Answer: No, we cannot restore up to Tuesday 8 PM.

Why not?

To restore to Tuesday 8 PM, we would need:

1. The most recent full backup before the point-in-time (Tuesday 2 PM) – which is missing
2. The differential backup from Tuesday 5 PM (which depends on the missing full backup)
3. The T-log backups from Tuesday 7 PM and 8 PM (which also depend on the missing full backup)

Without Tuesday’s full backup, the subsequent differential and T-log backups are unusable because:

• Differential backups only contain changes relative to their base full backup
• T-log backups require a starting point (full or differential backup) to apply the log records

Key Takeaways for Database Recovery

1. Full backups are critical: Losing a full backup can significantly limit your recovery options.
2. Dependencies matter: Differential and T-log backups depend on their base full backups.
3. Alternative recovery path: In this case, we could only recover to the last complete backup chain (Monday’s backups).
4. Backup verification: Regularly test your backups to ensure they’re complete and usable.

Best Practices to Avoid This Situation

1. Implement a 3-2-1 backup strategy (3 copies, 2 media types, 1 offsite)
2. Regularly verify backup integrity
3. Consider keeping multiple generations of full backups
4. Document and test your recovery procedures

This scenario highlights why understanding backup dependencies is crucial for effective database recovery planning.