It’s not enough to make sure backups exist, you have to know how to use them

It was going to be a nice, easy upgrade. Just a Microsoft Service Pack, practically identical to those I’d installed one hundred times before with no problems. Run to the server room just before bedtime when Internet traffic was lighter and do the upgrade.

This particular Service Pack was for Structured Query Language Server 7.0 and designed to wrap up outstanding issues relating to vulnerabilities to external hacking attempts. When I downloaded it I noticed it was rather large, over 35 megabytes, but that’s par for the course for Microsoft updates. Reading the release notes, one of the recommendations was to back up all databases before installing the Service Pack. That seemed entirely sensible, so I took the additional 10 minutes or so to back up the 12-odd databases that were running on a separate drive than the one SQL Server was installed on, just in case.

Turn off the services. Install the Pack. Long install. Still going … wow. OK, done. Restart the machine, restart the services, browse a Web site that depends on the SQL Server services. Nothing. Dead as the proverbial door nail. Check the services again. Check the OBDC drivers. Check the user permission lists. 11:23 p.m. and still nothing. I frantically scan the release notes to see how to roll back to the earlier Service Pack.

“This Service Pack cannot be removed easily because of system table changes that the service pack requires for maintenance. To revert to a previous build, you must remove and reinstall SQL Server 7.0 or MSDE 1.0 and, if required, apply the service pack (SP1, SP2, or SP3) that you were running before you installed this Service Pack.”

Oh that’s just great. Detach the databases, run to find the software, do a full uninstall and reinstall. It’s now 12:43 a.m. and fatigue is beginning to set in.

Halfway through the reinstall, during the installation of the Data Access Components, the reinstall hangs. And hangs. And hangs.

Reboot, try the reinstall again. Same result. About now, I can feel that faintly warm flush of near-panic coming over me as I realize how badly awry this simple upgrade has gone. I now have no working SQL Server and no hot-swappable replacement machine ready, only a spare computer with Windows NT that serves as a general-purpose spare.

It’s now 1:30 a.m. and I’d gladly declare defeat if having working Web sites didn’t depend upon getting the server backup. Suffice to say, that day stretched well beyond 4:00 a.m. and I faced still more challenges at 7:00 a.m.

Whose fault was the entire sordid affair? Microsoft could take some blame for not having an application that could roll back to an earlier Service Pack without the “option” of reinstalling, which is really no option at all. I could take some blame for not having a hot-swappable server ready to go, or an active one that was participating in database replication.

What saved me was having a complete set of backups. SQL Server, version 7.0 and the newer version 2000 provide extensive backup facilities, either to tape drives or hard drives. The user can specify writes of the actual database as well as the transaction logs. The transaction log is the record of every change to the database running in sequential order. When the server is shut down unexpectedly, as in the case of a drive failure, and then restarted, the database catches the current “state” up to the last written transaction log. This is known as “rolling back” or “rolling forward” the database.

On a system running or administrating banner ads, a once-a-day backup for both database and transaction logs might be appropriate. In the case of e-commerce transactions, backing up the transaction log at even 15-minute intervals might not be overkill. Banks or other transaction-biased businesses run sophisticated processes such as replication and clustering, which effectively leads to live and constant database backups. There are always a minimum of two instances of the database running the same data and transaction logs simultaneously.

In my situation, a backup that had aged one day in the case of database and transaction logs was perfectly adequate. However, I was unable to get the databases to restore properly due to simple inexperience with the process.

Fortunately, I had a contact that was able to assist with the restoration, and it taught me a valuable lesson: Having a backup is not enough. The ability to fully and completely perform a restoration at any time is absolutely critical.

I had done an adequate job configuring the backups by both backing them up to a RAID drive and making a once-a-day tape backup to protect against failure of the entire box and RAID controller.

The explosion of data at newspapers has made data backups even more of a challenge. Gigabytes and now terabytes of data can be a heavy load for many backup systems, and often tape is just not fast enough anymore. You’re just as likely to see a fiber-channel equipped drive array backing up another RAID as you are a traditional tape device, simply because the tape would have neither the capacity, nor the speed.

My experience certainly taught me a few things. One, back up every single piece of critical data you have. If it’s ultra-critical, back it up in at least two different locations, and take your archival copies off the premises. Two, make sure you not only have the backups, but that you know how to use them and are able to restore data at will. Three, don’t perform upgrades with the expectation of getting any sleep that night.

Hays Goodman is the webmaster for Newspapers & Technology and GMToday, a Milwaukee-area portal. He has been involved in professional Internet development for five years and welcomes your comments, feedback and suggestions for future Tips & Tricks columns. Write to him at webmaster@conleynet.com and include your contact information.