There seems to be a plethora of information regarding the process of setting up backup tasks, but not much information about the big-picture view of database backups. At least, it's hard to formulate a search engine query that gives you that information. I know there are three different types of backups:
- Full Database Backups
- Differential Database Backups
- Transaction Log Backups
It seems I'm supposed to use all three of them. So, is this a schedule that makes sense?
- 1st of each month -- Do a full database backup.
- Every day at midnight -- Do a differential database backup.
- Every 15 minutes -- Do a transaction log backup.
This way, if my database fails on, say, the 12th, I'd just restore the full database backup from the 1st, do the 12 differential backups from the 1st to the 12th, and then finally restore the most recent transaction log (are the transaction logs differential?).
Finally, is a full database backup self-contained? i.e. Once I make a full database backup on February 1st, can I delete all the files from January? Of course, I'd keep a couple previous months' sets around just in case, but the question is conceptual.