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?
Best Answer
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.