The purpose of transaction log in Restore First

Posted on

Question :

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

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *