SQL Server

• 06/23/1999

Protecting data integrity is one of the most important functions performed by a database administrator in a production environment. It's one thing if the system is slow and users are complaining that they can't complete their work in a timely fashion. It's a more serious matter, however, if the data is not available or has serious logical or physical errors.

I remember one situation in which the system administrator at a large health care organization did not adhere to clearly documented backup and recovery procedures. As a result, that person created a database backup that was corrupt. It took the organization a week to restore the mission-critical application to normal operations after a crash. Saving themselves a few moments of downtime each night by ignoring the correct procedures, the staff cost the organization thousands of dollars of overtime, generated considerable inconvenience to staff and patients and created serious potential liability for patient treatment errors. SQL Server 7.0 has several features designed to prevent such a fiasco. It's not fool-proof, however, so your operations staff must clearly understand the processes and procedures required to maintain acceptable data integrity.

One thing that may give you some peace of mind is that SQL Server 7 can usually recover the database automatically after a CPU crash. When the machine reboots, SQL Server 7 first rolls back any uncommitted transactions. Next it applies all committed transactions beginning at the last logical checkpoint in the transaction log file. When done, the only lost data should be associated with uncommitted transactions that were in-flight at the time of the crash.

You will need to consider a backup strategy that can be used to recover the database if you have a failure that SQL Server 7 cannot automatically recover from. For example, you will need to consider a hard disk crash. Another scenario may be a user or application error that incorrectly deletes data.

SQL Server 7 has three primary backup options: full backup, incremental backup and transaction log backup. Since SQL Server 7 supports filegroups, you can also establish a procedure for backing up individual filegroups, along with their transaction logs. This can be useful if one filegroup contains static tables, while a separate filegroup contains tables that are being actively modified.

A full backup makes a physical copy of all the pages in the database, including system and user tables, as well as the associated transaction log files. An incremental backup only copies the database pages that have been modified since the last full backup. A transaction log backup copies the transaction log files. The main difference between a full or incremental backup, and a transaction log backup is that the former two make a physical copy of the database pages, while the latter makes a copy of the logical operations -- INSERT, DELETE, UPDATE -- applied to the database. Since you need your transaction logs to bring the database up to date after you've recovered the full or incremental backups, you'll probably want to protect them, as well.

In most instances you will want to do a combination of all three. The actual mix will depend on a number of factors, such as the volume of data changes you need to capture, the amount of downtime that can be tolerated to perform a recovery and your tolerance for potential data loss. Some sites, for example, do a full backup once a week, an incremental backup nightly and a transactional log backup hourly. Once you've defined your backup strategy you have the choice of using the Microsoft Management Console to schedule backups, using the BACKUP DATABASE Transact-SQL command or writing your own backup utilities that call SQL-DOM. --Robert Craig is vice president of marketing at WebXi Inc. (Burlington, Mass.), and a former director at the Hurwitz Group Inc. Contact him at rcraig@webxi.com.