For those of you who have had me in class before, you have heard me talk about the benefits of applying the SIMPLE recovery model. And you have heard me urge its use if you can get away with it. You have also heard Microsoft write about the benefits of complex multi steps DR plans for huge enterprises. Recently I ran into a situation where an approach right in the middle was called for, and I’d like to share that with you.
Back up and restore scenarios are all about 2 things. “Mean time to recovery” and “Acceptable data loss”. Weaved into the art of leveraging these components are idiosyncrasies to your own environment. Recently I ran into this environment...
12 gig Mission critical database
Restore operations MUST be quick and simple
24 hour data loss is acceptable
Disk space for retention of backups is limited (about 60GB available)
Daily transactions touch about 2-10% of the total size of the database (through inserts or updates, deletes almost never occur)
A traditional backup up schedule of a complete every night will not be sufficient, it will provide simple restores and handle the 24 hour data loss requirement, we simply will not have enough space for sufficient retention of backups.
On the other side of the spectrum , we could change to FULL recovery model and run a complete weekly, run transaction logs backups every 15 minutes, then run differentials every night. This option is unacceptable because the restore process is not simple enough, yet it does handle the disk space issue.
Consider this setup. Stay with the SIMPLE recovery model. Set up weekly complete backups and set up daily differential backups. This keeps restores relatively easy, it will always be a two step process – restore the complete then restore the most recent differential (no transaction logs). It also conserves disk space as we only have one 12 GB backup (the complete) per week, and the nightly backup is a differential of no more than several hundred MB (though this will grow throughout the week).
If you have a relatively large database that is mission critical but has few inserts into it daily, and quick/easy restorations is of utmost importance, consider the approach I just went over.
- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed