I'm trying to convince a client that their SQL Server backup strategy is insufficient. Long story short is that their current backup "strategy" is a weekly full backup followed by daily transaction log backups. I have already advised the client that, due to the size of the database, they should switch to a daily full backup strategy accompanied by multiple daily transaction log backups or at least add a daily differential backup to what they already have in place. However, they are floundering around with this because it will require much more drive space than they already have. So, my question is - what are potential problems or risks that could be presented for a backup strategy that is only a weekly full backup and daily transaction log backups? Is it mainly that it may take hours or days to restore the database because the t-log restore has to re-run each transaction after the full backup is restored?
Here are the specs of the database for sizing and activity context:
- Database Space Used: about 12.5GB
- A weeks worth of transaction log backups: 7 TRN files with a sum total size of about 400 MB
- A weekly post-reindex transaction log backup: about 3GB