Log backup vs differential backups

Question

We're trying to define a backup policy. We were going to apply following:

• Weekly full backup, retained for 30 days.
• Nightly differential backups, retained for 7 days

But we're also exploring other option - Transaction Log backups, and Azure backup(which is offering a default of 2 hours)

Am I right in saying that that the differential backup would get us to the last differential backup, and then we can use log backups to get closer to where we want to recover from?

So, backups will be as below:

• Sunday 1am- Full backup.
• Monday 1am - differential
• Tuesday 1am - differential
• Wednesday 1am - differential

Every 2 hours since Wednesday Inc - Log Backups.

Lets say Disaster hits at Wednesday mid day!

Recover the differential , which gets us to Wednesday at 1am... Then we use Log backups to get us to before the disaster struck.

Is that the correct distinction between the types?

Answer 1

We're trying to define a backup policy.

You are starting from the wrong end of things.

Backups are only the tools that you use to support a Recovery Strategy.

Start by working out - in conjunction with your Business partners; you cannot do this alone - how long you can take to get back a "broken" database (Recovery Time Objective) and how much data they're prepared to lose while you go about doing so (Recovery Point Objective).

Once you understand these two constraints, you'll have a better feeling of what you need your backup tools to do.

Answer 2

Yes, to restore to the point you want you need to:
1. restore full backup
2. restore the last (or another required version if you don't want to restore to the most recent point) differential backup
3. restore chain of transaction log backups
You can skip 2. but in this case you will need to restore more log backups and it may take more time

Answer 3

I used to think Microsoft Azure takes a transaction log backup every 5 minutes. A quick explanation on the backups.

The Full backup backs up the whole database, your differential which I believe is Incremental backs up the changes made since your last full back up while the Transaction log backup is the back up of the changes made since your last differential/log back up.

You need to note If you are taking transaction log backup every 2 hour, then you probably might loose 2 hours worth of data because you can only restore to the point of the last log backup taken.

So From your scenario, Wednesday Midday, you would have a full backup on Sunday, Differential from Wednesday 1AM and transaction log backups at 3, 5,7,9 and 11AM to restore from at 12 noon on Wednesday.

In addition to that, you also need to understand your company RTO and RPO to understand how much data the company is fine to loose and how much time is needed to put your databases and applications back on line after a disaster.