How to set up a stable backup plan?

Our database is more than 20,000,000 bytes and increase largely everyday

who can give me some advise about stable backup?

My database is sqlserver2005 and now i use files to backup including full backup and diff backup,whether is better to use device?

any ideas? thanks in advance

Your backup plan will depend on your recovery point objective (RPO) and recovery time objective (RTO). How long should it take for you to restore your databases should a disaster strikes? How critical is your data and from when do you need to have your data restore? You should also look at your database recovery model as this will affect your RPO and RTO.|||

thanks in advance,however RPO and RTO seem a little different for me,can u give me some details about that? and any ideas about what i did now

1.I first set up maintenace plan ,backup full and diff database to files and specify schedule

2.set up a job,type is ssis and package is from "maintencace plan/***" and specify schedule same with backup plan

two issues:

1.whether i need to backup transaction log in maintenace plan?

2.what's the difference backup to files and backup devices? which is better?

I think if i write T-SQL script in job and backup database to device, looks simple,is it right?

any ideas about thisT-SQL script?

much appreciate

|||

Hi Andrew,

RTO is the maximum amount of time that the service can be unavailable whereas RPO is the maximum amound of data that can be lost following a disaster. So, your requirements may be to have the service back up and serving clients within 2 hours following a hardware disaster (RTO), but you could afford to lose 1 full day's worth of data because, for instance, it's easily reproduced (RPO).

Define your RPO and RTO requirements first, then a stable, suitable backup plan can be created to meet the RPO requirement. Although your chosen backup strategy will contribute to meeting your RTO objective, RTO is usually fulfilled via high-availability options such as clustering, mirroring, offsite dr etc. (assuming, of course, that your RTO dictates a high-availability solution!)

For an example, you could define a daily full backup job at 00:00, followed by a tran log backup job every 15 minutes. This would give you an RPO of 15 minutes.

Cheers,

Rob

|||

much appreciate for all