Could your business survive if you lost all the data in your Microsoft SQL Server database? What would be the impact of losing months of email from your Microsoft Exchange server? Unfortunately, backups tend to be low on the priority list until something bad happens, at which point all your users expect you to recover their data immediately. In this article, I look at the Windows NT backup program, consider some alternatives, and discuss factors that might influence your choice of backup strategy.
Backup Philosophies
You can take one of two approaches to backups: You can back up the server, or you can have users back up their workstations. When you back up the server, you typically back up everything on the server, including user files that you store there. The amount of data at stake in a system crash or a natural disaster is considerable, probably on the order of gigabytes. Therefore, you need a high-capacity tape drive or an alternative high-capacity system such as DVD-RAM—and of course, you must plan for off-site storage of the backup media. If restoration becomes necessary, you might have to restore the entire system, a task that could take some time.
The workstation backup approach is appropriate for the user who accidentally deletes a file or wants to use last week's version of a file instead of the current version. These situations usually involve a much smaller volume of data (maybe a few megabytes and often just a single file) than a system crash or major disaster. For backup protection at this level, you can equip users with some type of backup device, such as a Zip drive or LS-120 high-capacity disk drive, and trust them to use it. However, most users are not going to take time to perform backups. You are usually better off to require users to store their data on the server.
As a systems administrator, accept responsibility for backing up only data that is stored on the server and use a tape drive to back up the crucial portions of the system. You also might want to install some other backup device (e.g., DVD-RAM, CD-Rewritable—CD-RW) that offers instant access to files, and let your users use it for frequent backups of their data. If any users insist on keeping their data on their workstations, they also must take responsibility for their backups. This strategy means that the administrator (or whoever performs the backups) does not need to access the individual users' workstations. You thus maintain a high level of security.
The Price Is Right
NT comes with a built-in backup program (ntbackup.exe), which Screen 1, page 182, shows, that is sufficient for basic backups. However, this program lacks features that make it easy to configure, such as the ability to schedule backup tasks.
The NT backup program seems to have problems with building catalogs of the files it backs up, making restores more difficult than they need to be. (For more information about these problems, see the sidebar "The Mysterious Disappearing Tape Catalog," page 183.) Many administrators like to replace the NT backup program with a third-party product such as Cheyenne's ARCserve, Seagate's Backup Exec, UltraBac, or NovaStor. These products allow scheduling with various levels of flexibility and ease of use. However, each product entails additional cost and maintenance.
Do You Need an Agent?
The NT backup program cannot back up open files. Therefore, it cannot back up files such as SQL Server databases that are in use. Third-party programs typically offer agents, usually at extra cost, that can back up open databases. If you need to back up several Microsoft BackOffice applications, the additional cost for the agents can add up quickly. If you prefer to avoid the higher costs, another solution might work for you. For example, the backup option built into SQL Server can back up data to a disk file. Once the backup is complete, the backup file is no longer open—so you can back it up using any basic NT-based backup software. An additional benefit to this approach is that backing up to a disk file is faster than backing up to a tape, so this method minimizes the slowdown time for your database or messaging server.
SQL Server 6.5 has some scheduling capability, but SQL Server 7.0's scheduling capability is far better. SQL Server 7.0 lets you set up a sequence of jobs that perform the SQL Server backup to a disk file, then run a batch file to start the NT backup of that disk file to a tape drive.
File vs. Image Backups
A popular feature that the NT backup tool does not offer is image backup. NT (and many other software packages) backs up files individually and restores them the same way. Using this restoration method usually means you must reinstall the OS and the backup software, then perform the restore. An alternative that UltraBac and ARCserve offer is the option to perform an image backup. This method backs up the disk sectors to tape without regard to file delineation. The restore process then puts the sectors back on disk. With a set of boot diskettes, you can restore from a tape without having to reinstall NT. Both of these packages also let you extract a single file from the image backup, giving you the best of both worlds.
Backup Device Choices
A bigger variety of backup devices exists now than ever before, but the real choice is between sequential media, such as tapes, and random-access media, such as disks, removable drives, or CDs. The following seven factors might influence your selection.
Type of backup required. Consider whether you usually back up the entire system or just the crucial files. If you back up the entire system, you need the ability to back up gigabytes of data, and tape is your best solution. If you back up only crucial files, you can get by with less storage.
Type of restore required. Do you anticipate having to restore the entire system from scratch? If so, a large-capacity tape drive might suit you. Are you more likely to need to restore one file a user has deleted accidentally? You might be better off with a CD-Recordable (CD-R) or CD-RW drive, or even a removable disk drive. If you have ever had to restore just one file from a tape backup, you know that finding the correct file can take hours. Some of the new-technology tape drives are much easier to use, but none let you select the file directly, as you can with a CD-R or disk drive. If you have to meet both needs, you might want to consider a mix of backup types, using both sequential and random access devices.
Speed of backup and restore. Tape is notoriously slow. Even with recent advances, tape is not very fast compared with a hard disk. The fastest backup medium is another hard disk, followed closely by removable drives, then CD-R, CD-RW, and DVD-RAM.
Capacity. Tape capacities have not kept up with the incredible progress in the size of hard disks. Plan to back up the entire system regularly and crucial files frequently, and you can easily calculate the capacity you will need. Be careful about how the manufacturers state capacity. The industrial-strength drives such as Exabyte usually list their native capacity. The so-called consumer drives for home use almost always claim twice that capacity, assuming (in fine print) a compression ratio of 2:1. That compression ratio is realistic (e.g., I always plan to pack 4.4GB of data on my 2.2GB Exabyte drive), but because various types of files compress differently, your compression ratio depends on your data.
Initial tape drive cost. For small businesses, the very high-end tape drives, such as DLT, are overkill. Large enterprises with significant volumes of data use these tape drives. At a cost of several thousand dollars, DLTs cost more than most small businesses want to invest. At the opposite end of the spectrum (i.e., $200) are the quarter-inch cartridge (QIC) tape drives, which connect to the 3.5" disk drive controller. These drives typically work fine for home use, but I would not put crucial business data on them.
Tape drive lifetime. Generally speaking, the more expensive the drive, the longer it will last. The less-expensive drives are for home use, or perhaps for a small office that backs up one computer. Higher-priced drives are for daily backups and have a longer life expectancy with heavy use than the less-expensive drives.
Another way to evaluate a drive's useful lifetime is to look at how often the related technology changes. The pace is a little slower for business-oriented drives than for drives in the consumer market, where new products compete for shelf space with products only a few months old. If something happens to your drive, you might want to replace it with a comparable type so that you can read your valuable backup tapes. If the drive has been obsolete for a couple of years, you might end up with a stack of tapes and no way to read them.
You will probably outgrow the capacity of a high-end tape drive based on leading-edge technology before the drive wears out. For example, my Colorado Memory Systems DJ-250 is still working. The system was great in 1991 when I had 200MB hard disks and could back up an entire drive using data compression. Then when I went to 1.2GB drives, an Exabyte 2GB tape drive seemed to be more than I needed. This unit was built like a tank—but is now obsolete. Now, my primary computers are running multiple 12GB hard disks, and it's time for a new tape drive.
Cost of media. Reviews of backup devices almost always ignore tape cost. Try this approach to estimate your tape cost: Determine how much data and how frequently you need to back up. Factor in the length of time you plan to retain the backups. Then, estimate how many tapes you need for a given tape capacity. Remember to include offsite and permanent archive tapes. With this information, you can calculate the total cost of the tape drive and tapes. The results might surprise you.
The more expensive drives, such as the 8mm drive, often use the least expensive tapes, ranging from $5 to $12 each. The cheaper drives often use more costly tapes, ranging from $30 to $40 each. Which is the better investment? Let us look at a real-world example. The Exabyte EXB-8700LT external tape drive costs $665; the Seagate TR4 drive costs $225—a substantial difference in purchase price. The 8mm tapes that the Exabyte drive uses each hold 7GB of uncompressed information and cost $12. The TR4 drive's tapes each hold 4GB of uncompressed information and cost $36. Let's say you back up 4GB of data from your server each day, so you don't use the 8mm drive's full tape capacity. Three weeks' worth of tapes is 21 tapes. What is the cost of a drive plus 21 tapes for each example? The EXB-8700LT drive and 8mm tapes cost $917. The TR4 drive and tapes cost $981.
Supporters of the cheap tape drives usually reject this argument and claim that I am stacking the figures in favor of the expensive drives by using 21 tapes when they can get by with 4 or 5 tapes. Well, tapes wear out. The number of times you can use them is limited. With my data, I want to be able to do a backup without having to think about the tapes' condition.
Not a Perfect World
You have many choices for backing up your data, but all of them involve some compromise or trade-off. As a result, you might have to implement more than one backup strategy and live with the requirement to support multiple devices, software packages, and media types. For more information about this subject, see "Related Articles in Windows NT Magazine."
Also, make sure that once you have established your backup policy, you write it down and make it a part of your company computer policy. For example, if you decide to rotate your backup tapes, and you have enough tapes to perform backups on a 3-week rotation, make 3-week tape recycling your written policy. I was recently in a class with someone from a company that had been hit with a product liability lawsuit. The opposing lawyers accused this company of deliberately destroying evidence because the company had recycled its backup tapes and could not produce copies of some old documents. However, company personnel were able to show the judge that they had always recycled tapes because it was part of their documented operating procedure. The written policy dated back to before the lawsuit was filed, showing that the company had not suddenly begun erasing tapes in response to the lawsuit. The judge ordered the company to stop erasing tapes, or at least retain an archival copy of all data, from that point forward. But the company was cleared of the charges related to destroying evidence. The necessity to think of these things is an unfortunate reflection of a litigious society. However, with the legal system requesting old email messages and other electronic data as evidence, how long you keep backups and what you decide to back up are not just technical decisions anymore.
