I was spring cleaning some old scripts and I came across my first attempt at establishing a backup strategy. 12 hours later I’m still feeling a bit queasy. When I first put on the DBA hat, we were running with the simple recovery model and doing a full backup every night. I did some homework and thought I’d got it all figured out. My killer improvement? Use full recovery and every night do a log backup and then a full backup. That way we could recover to any point in time. Brilliant!*
Since then I’ve learned a lot. I know I’m much better than I was. But I can never quite shake that small nagging doubt: how much of what I’m doing now – believing it’s good practice – will I one day look back on and shudder? But this is a good thing. It keeps me on my toes, continually re-evaluating what I’m doing, and hungry to learn.
*Just to be clear, I’m being sarcastic. This NOT a good solution. If you have any doubts:
- Read Books Online about recovery models and backups.
- Read what Paul Randal has written about it in his blog and in TechNet Magazine.
- Check out Ola Hallengren’s database maintenance solution before you write your own.