Many different solutions are available for backing up data. Some are fast and expensive. Others are slow but very reliable. The backup solution that is right for your organization depends on many factors, including the following:
Capacity. This refers to the amount of data that you need to back up on a routine basis. Can the backup hardware support the required load given your time and resource constraints?
Reliability. The reliability of the backup hardware and media determines how useful the backups you create will be when you need them to restore lost data. Can you afford to sacrifice reliability to meet budget or time needs?
Extensibility. The extensibility of the backup solution refers to its ability to expand beyond its original capacity. Will this solution meet your needs as your organization grows?
Speed. Consider the speed with which data can be backed up and recovered when selecting an appropriate solution. Can you afford to sacrifice speed to reduce costs?
Cost. The cost of backup solution choices will affect your decision. Does the solution fit within your budget?
Capacity, reliability, extensibility, speed, and cost are the main issues that will influence your choice of a backup plan. If you determine the relative value of these issues to your organization, you will be able to select an appropriate backup solution for your situation. Some of the most commonly used backup solutions include the following hardware and media:
Tape drives. Tape drives are the most common backup devices. Tape drives use magnetic tape cartridges to store data. Magnetic tapes are relatively inexpensive, but they are not highly reliable. Tapes can break or stretch. They can also lose information over time. The average capacity of tape cartridges ranges from 4 GB to 10 GB. Compared with other backup solutions, tape drives are fairly slow, but their biggest advantage is low cost.
Digital audio tape (DAT) drives. DAT drives are quickly replacing standard tape drives as the preferred type of backup devices. Many DAT formats are available. The most commonly used format is Digital Linear Tape (DLT) or Super DLT. With DLT IV, tapes have a capacity of either 35 GB or 40 GB uncompressed (70 GB or 80 GB compressed). If yours is a large organization, you might want to look at Linear Tape Open (LTO) or Advanced Intelligent Tape (AIT) tape technologies. Typically, LTO tapes have a capacity of 100 GB uncompressed (200 GB compressed), and AIT-3 tapes also have a capacity of 100 GB uncompressed (260 GB compressed).
Tip
To perform faster backup and recovery operations, you can use multiple backup devices with SQL Server. For example, if it normally takes four hours to perform a full backup or restoration of the database, you can cut the backup and restoration time in half by using two backup devices; with four backup devices, you could fully back up or restore the database in an hour.
Autoloader tape systems. Autoloader tape systems use a magazine of tapes to create extended backup volumes capable of meeting an enterprise’s high-capacity needs. With an autoloader system, tapes within the magazine are automatically changed as needed during the backup or recovery process. Most autoloader tape systems use DAT tapes formatted for DLT, LTO, or AIT. Typical DLT drives can record up to 45 GB per hour, and you can improve that speed by purchasing a type library system with multiple drives. In this way, you can record on multiple tapes simultaneously. In contrast, most LTO and AIT drives record over 100 GB per hour, and by using multiple drives in a system, you can record hundreds of GB per hour.
Optical jukeboxes. Optical jukeboxes are similar to autoloader tape systems. Jukeboxes use magnetic optical disks rather than DAT tapes to offer high-capacity solutions. These systems load and unload disks stored internally for backup and recovery operations. The main disadvantage of optical jukeboxes is their high cost.
Removable disks. Removable disks, such as an Iomega Jaz disk with 1 GB or 2 GB capacity, are increasingly being used as backup devices. Removable disks offer good speed and ease of use for a single drive or single system backup. However, the disk drives and the removable disks tend to be more expensive than standard tape or DAT drive solutions.
Disk drives. Disk drives provide the fastest way to back up and restore files. Using disk drives, you can often accomplish in minutes what takes a tape drive hours. So when business needs mandate a speedy recovery, nothing beats a disk drive. The cost of disk drives, however, may be higher compared to tape library systems.
Selecting a backup device is an important step in implementing a backup and recovery plan, but it is not the only step. You also need to purchase the tapes or the disks, or both, that will allow you to implement your backup and recovery plan. The number of tapes, disks, or drives you need depends on:
How much data you will be backing up.
How often you will be backing up the data.
How long you need to keep additional data sets.
Typically, you implement backups by using a rotation schedule with two or more sets of tapes, disks, or files on a drive. Having more than one set of media allows you to increase media longevity by reducing media usage, and at the same time it reduces the number of actual tapes, disks, or files you need to ensure that you have data available when necessary.
Best Practices
For important databases, I recommend using four media sets. Use two sets in regular rotation. Use the third set for the first rotation cycle at the beginning of each month, and use the fourth set for the first rotation cycle of each quarter. This technique allows you to recover the database in a wide variety of situations.