Why do we do weekly and monthly database backups – why not just daily?

All we need is an easy explanation of the problem, so here it is.

I’m sure I’m missing something obvious here, but if I’m doing a full database backup every day, why do I also need separate weekly and monthly ones? Or is it a case of not needing separate weekly and monthly ones, but just setting a daily one aside once a week and once a month?

Thank you.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I doubt there’s a "we" that can answer your question — it all depends on your specific requirements. If, for some reason, you want to be able to restore the image of your data as of exactly 7 days ago, but not 6 or 8, sure, hold on to your weekly backup.

If, on the other hand, powers that be require you to be able to provide a snapshot of your data at any instant within the last seven years (this happens), you have a choice.

You can keep just one base backup taken at the beginning of time, plus all of the transaction logs since then, to facilitate point-in-time recovery (PITR). Recovery, though, might take about the same time that it took to run all the transactions, consecutively, since that first backup, as you’ll be replaying all the logs between then and the recovery target time.

Alternatively, you can keep daily (weekly, monthly) backup images, plus all the logs for the next day (week, month), in which case you’ll use more space but your recovery time will be reduced proportionally.

If, however, you don’t have these "go back in time" requirements, you don’t really need to keep more than one backup image, taken with the frequency that your RTO (recovery time objective) demands.

Method 2

In addition to mustaccio’s answer.

We are talking about the Grandfather-Father-Son (GFS) long-term Retention Policy.

This is a backup retention policy based on cycles:

Grandfather – monthly backup

Father – weekly backup

Son – daily backup

Such a backup retention policy allows finding a sane balance between the data recoverability and costs spent on the backup infrastructure.

Note: this is not about when to perform backups, it’s about which backups should be deleted from the storages and which ones should be kept for a long time.

Additionally:

If a backup source supports incremental backups, then incremental backups can be performed as daily backups and full backups as weekly and monthly backups.

If the backup source supports incremental and differential backups, then incremental backups are used as daily backups and differential backups as weekly backups.

All of the above is not a dogma – it’s just good practice – which fits most business requirements.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply