julia.nicholas (3/11/2008)
I'm trying to figure out a backup strategy for SQL 2005. I need to have a recovery routine that minimises downtime, so I figured I'd go with a daily full backup, 4 hourly differentials and translog backups every 30 minutes.I'm concerned about the backup files getting oversized and having to fish through multiple backup sets looking for the right files to recover in the right order so I need to decide when to INIT the backup devices, and if indeed, it is a good idea to have 3 backup devices (one for full, one for the diffs and one for the translog backups). I came up with the idea of running the following steps in my daily backup job:
STEP 1 - backup database to fullbackup_device WITH INIT
STEP 2 - do differential backup to diffbackup_device WITH INIT
STEP 3 - do translog backup to translogbackup_device WITH INIT
In the 2 hourly differential backup job just do a diff backup to diffbackup_device WITH NOINIT.
In the translog backup job do a translog backup to translogbackup_device WITH NOINIT.
I need a sanity check. Is this a sensible plan or I'm I missing the blindingly obvious?
Hi,
First thing, you need to ask the business, what are our Recovery Time and Recovery Point Objectives, as these thresholds will drive your backup strategy. Otherwise, you risk letting technology drive your backup plan, which is not a good way. Once you have both of these values, you will need to devise a plan to meet or better exceed the RTO and RPO expectation for the business.
So, as an example, the business provide you with a RTO of 1hr (back in business) and a RPO of 30min (what we can afford to loose in data terms). Now you have something to work with and you decide to do following:
1. Full backup - Daily 20:00
2. Differential backup - Every 4 hours
3. Transaction log backup - 30min
So this means, in event of a failure, you get back within 30min from point of failure, maybe more if you can backup tail of transaction log. Now, achiving the 1hr RTO is going to depend on how fast you can restore your full backup, your latest differential and transaction log to point before failure. The only way to ensure you can achive this is to test, test and test again. Most importantly, you will need to ensure you allocate ample storage to support your strategy, so this is a key requirement to allow a smooth recovery process. The more log dumps you have, the more management you'll need to perform in terms of making sure the backup files are safe.
Another way of achieving the RTO and ROP would be to look at other features of SQL Server, such as log shipping and database mirroing, which should be included in your DR strategy, as these methods are faster then playing backups.
Finally, if file size and storage utilization are major concerns, look at implementing tools such as Red-Gate or Quest Light Speed backup products, as they allow you to compress your backup files.
Thanks,
Phillip Cox
MCITP - DBAdmin