Restore Database - Simple Recovery Model

Question

I am working on below exercise. I would have thought the answer is "Restore the latest full backup. Then, restore the latest differential backup".

However, the answer given is "Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup".

I didn't think this is correct as Transaction log backups not taken on Simple mode?

Thanks!

Scenario: The database uses Simple Recovery model. Full database backup 01:00 daily. Differential backup 13:00 daily.

Issue: The differential backup fails. Then database fails at 14:00. How to restore database and ensure minimal data loss?

You are partially right -- SQL Server won't even let you CREATE log backups, when set to "simple" recovery model. I'll post an answer and say more. — Doug_Ivison

Doug_Ivison Doug_Ivison · Accepted Answer · 2013-10-23T14:56:22

Both answers are incorrect: your and theirs.

You are right about one thing -- SQL Server won't let you even CREATE log backups, on a database set to "simple" recovery model.

So their answer is incorrect, because it says "restore each log backup", when log backups cannot exist. However, your answer is incorrect, also, because there was ONLY ONE DIFFERENTIAL BACKUP since the full backup, and THAT DIFFERENTIAL BACKUP FAILED.

So... the real answer is:
(1) Attempt to make a backup of the failed database.
This cannot make things any worse, and if it succeeds, might be very useful later. (If it has very important info, you can try restoring it to an alternate environment later, and see if any of that info can be recovered.)

(2) Restore from the latest full backup.

Questions?

Restore Database - Simple Recovery Model

2 Answers