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.
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.
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.
