1. How much data is acceptable to lose?
2. How large are your databases?
3. How often does data change?
4. What is the maximum amount of time a system can be offline for a recovery?
5. What media do you have available and what is the capacity?

You plan your backup strategy around the business requirements of system downtime and data loss. You plan the media to be used based upon the technical parameters of your databases and the requirements for system downtime. Remember the number on job of any DBA is to protect the data.

How much data is acceptable to lose?

You will have an argument that needs to be overcome here. The answer that will always be received to this question is – zero. Every administrator seeks to prevent any data loss, however guaranteeing no data loss is an impossibility. Systems can and do crash. Data can and does get lost. This is a fact of life in the world of computers that can not be overcome.

The decision makers need to be aware that the closer they get to a guarantee of zero data loss, the most the backup solution will cost. There are overall backup solutions that can be implemented to get you very close to this goal, but the cost is prohibitive to many companies.

The better way to phrase this would be: What is the maximum amount of data that can be lost from the database that can not be easily recreated? This is generally get a much better answer to work with. In most companies, this will be anywhere between 15 minutes and 6 hours. You will generally see anywhere between 15 minutes and 1 hour being used.

How large are your databases?

This parameter determines the capacity of the backup solution that you will require. Since you always want to have a portable backup that can be stored offsite, this parameter usually determines your tape requirements.

How often does data change?

This determines the frequency with which databases and transaction logs are backed up. Data that is static needs only a single back up. Data that is derived from another source probably doesn’t need to be backed up at all unless it would tae much longer to derive than to restore. If portions of your database are static, then these might be candidates to place on their own file or filegroup and backed up only once. The volititlity of your data mainly determines the frequency with which differential and transaction log back ups are taken so as to minimize the backup time and control the size of the transaction log.

What is the maximum amount of time a system can be offline for a recovery?

This parameter has the most effect on your backup plan and media used. If you have a 10GB database that can only be offline for a maximum of an hour, then you have just ruled out using DAT tapes and will most likely be utilizing a disk dump device. You would also want to keep the number of transaction logs that need to be restored to a minimum. The maximum allowed recovery time is usually the driving factor in designing a backup strategy.

What media do you have available and what is the capacity?

This will tell you how many tapes you will need for a backup or how much disk space it will consume. This will also determine the maximum restore speed that you have available. In the case of network backup devices, it also tells you if connectivity to another machine will be required before restoring a system. Disk is always better than tape in terms of speed and capacity. Tape is portable and allows you to store backups off site where disk is not.

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32