I am trying to understand how the backup of db and tlogs works and if my scheme is a good one, and how a restore process would work with it.
I have ALL DBs set to backup once per week, FULL, all going to individual bak files per db. Expiry is set to 31 days.
I have ALL DBs set to backup once per DAY, DIFFERENTIAL, all going to individual bak files per db, TO THE SAME directory as the Full backup above. Expiry is set to 31 days.
Half of my DBs are set to FULL logging, which I think I need. I have ALL DBs set to backup TLogs every 4 hours, 1 file for each log, going to a differnt directory than the bak files. Expiry is set to 7 days.
The backups are being stored on a different physical disk than where the MDF and LDF files exist. I am verifying integrity in all 3 backup operations.
1. Is this a good scheme?
2. Do the Diff backups append to the same files as the Full backups since I have them pointing to the same directory?
3. Is 31 days of retention for bak and 7 for trn adequate?
4. Say the worst happened, what would be the restore process? Would I just choose to restore a db and sql server would display all my full, diff and tlog backups history and I would just check off the ones that I wanted to restore or is the process different?
5. Am I missing anything else that I need to do to ensure a proper backup scenario?