1

Based on this article , the golden role for restore first

After restoring full database backup, restore latest differential database backup and all the transaction log backup after that to get database to current state.

Differential backup saves all the data changes since the last full backup.

I dont understand why must we restore transaction log backup since ( full database backup + differential backup ) should restore my database to its latest state.

My apologies , I cannot think of a better question title

  • Are you taking log backups? Great, if so, restore them. Are you not taking log backups? That's unfortunate, but you can't restore what you don't have. – Reaces Dec 17 '14 at 12:30
  • The differential backup restores the database to the Point the differential backup was taken, not to the latest state. The log backups allow you to restore to any point in time, the log backups covers. – Sunzi Dec 17 '14 at 13:12
  • Basic reason is ONLY LOG BACKUP restore can give you point in time recovery – Shanky Dec 17 '14 at 19:16
5

Restoring a full + differential backup restores you to the point in time that the differential backup started. If you just took the differential this second and nothing has happened in the database since it started, then yes, that is the latest state. But it's quite unlikely that you will have a disaster the minute you take a differential backup and that nothing has happened in the meantime.

Which means you can't restore to the point in time right before the disaster - everything that happened between the differential and <now> is gone. If you take a differential every hour, this means that disaster could strike at hh:59:59.9999999 and then you'd have lost almost an hour's worth of data.

This is the whole point of the full recovery model - to minimize data loss. If you're not going to bother taking log backups, then switch to simple.

If you take transaction log backups in between differentials (or just don't bother with differentials, since all they do is reduce the number of transaction log backups you need to restore in the event of a disaster), then your maximum potential data loss is your log backup schedule. Because with log backups, you can apply the following during a restore:

  • restore latest full backup b
  • restore latest differential backup d taken after b
  • restore n log backups taken after d up to point in time t

You need to decide what your tolerance is for data loss in order to determine how often you should be running transaction log backups. If you don't know what your tolerance is, you need to discuss this with management / stakeholders.

Could you just take a differential every minute? Or take full backups every five minutes? Sure. But buy some bigger disks.

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.