0

I'm working with a number of different servers that are severely lacking in backups.

I want to implement a back up plan using simple mode with full backups weekly followed by a series of differential backups throughout the week.

Is it a viable plan to perform the full and differential backups by overwriting the same file? Again I am trying to limit the amount of storage used and from my understanding if I'm taking a differential backup on some interval the data is cumulative therefore only the most recent .dif file in conjunction with the full .bak file will be necessary. Therefore each time a differential backup is performed it would overwrite the previous. Is this safe or am I misunderstanding something crucial?

Improve this question

3 Answers 3

Reset to default
5

Each differential backup is composed of the pages that have changed since the last full backup. So if you take a full backup on Sunday, then diffs on Monday, Tuesday, etc. then restoring the database will only require Sunday's full backup, followed by the most recent day's differential.

However, by overwriting your diffs each day, you are losing the ability to restore to a previous point in time. Example: Let's assume you take backups at 1 AM daily. On Thursday afternoon, you discover that data got corrupted on Wednesday and you need to get back to the last known good state. You can't, because the differential backup at 1 AM Wednesday (which would be clean) has been overwritten by the Thursday 1 AM backup which is corrupt.

So to answer your question, it is safe if and only if you assume that you will never have to revert to anything but the most recent backup, and that backup is always correct and corruption-free. I am not staking my job on that assumption.

I would not endorse the scheme you're proposing. Storage is relatively cheap - how much is the data worth to the company? Move the backups off to another, larger, cheaper datastore on a regular basis if space is at a premium, so that you can comply with any data retention policies, regulations or laws as needed.

Improve this answer
1
  • I didn't consider corruption between differentials, thank you. I think I will have to re-consider this plan by retaining differentials at least between full backups.
    – Jeremy S.
    Commented Jan 7, 2020 at 18:40
0

If you overwrite the diff backup everytime and there is an issue with your latest diff backup, your only remaining option would be to restore the full backup (with potentially +6 days of data loss).

What is your RPO for those databases (how many data are you allowed to lose) ? If the data is not important and you are allowed to lose some part of it, then that option could be valid, but I would prefer to keep at least 2 full backup (in case the last Full backup have got corrupted for example) and the last 2 diff backup (for the same reason).

Again, the only way to know if your plan is good is to know what are your requirement (RPO/RTO)

Thanks

Improve this answer
0

That's correct, each differential which is taken brings your recovery point to the timestamp of the differential. If you overwrite the differential backup each time it is run you will not be able to recover a database the time between the FULL and the differentials which were overwritten, only either from the FULL only or the available differential. If this scenario is acceptable with regard to your RPO you are good to go. If not, you would want to rethink and possibly keep the differentials for a specified time frame.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.