Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 9

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 9 With disk fault tolerance installed on the transaction log volume only, and with systematic database and transaction log dumps, your data is also safe. If you have a full database dump and subsequent transaction log dumps, you can easily restore your data in case of a failed data drive. This recovery involving the data drive will, however, require the system to be brought off-line. If you lose a transaction log drive, this volume is protected by fault tolerance, and recovery will consist of only replacing the failed drive and letting the controller rebuild the new drive to full fault tolerance. With no fault tolerance installed, you run into a risk of losing transactions even in the case of frequent backups. Any changes to your database that occurred after your transaction log dump will be lost if your transaction log drive fails. In addition to a high possibility of data loss, recovery will require the system to be taken off-line. More detailed information on the backup and recovery procedures in various failure scenarios, refer to the SQL Server Administrator's Guide. Your ability to recover your data depends entirely on your data protection scheme. This protection scheme can consist of various methods, such as implementing database and transaction log backups alone, combining these backups with fault tolerance, or even taking advantage of replication. In this section, we'll provide you with some more insight into backup and recovery options available with Microsoft SQL Server for Windows NT, and refer you, whenever possible, to a more detailed source of information. Database backup and recovery consists of the database administrator archiving data onto a secondary storage on a scheduled interval, and in the case of a failure, restoring the data from this secondary storage. Secondary storage can be a remote backup server, a local hard disk device, a local tape device or a local optical disk device. Database archiving can be accomplished in two ways. The database administrator can shut down SQL Server, thus closing all database and transaction log files, and then perform a file-based backup of all SQL Server devices. This type of backup is called off-line backup. It is necessary to stop SQL Server to gain access to the files, since SQL Server keeps all database and transaction log files open with exclusive access as long as the database engine is running. Therefore, any attempt to backup these files while SQL Server is running will fail. Or, the database administrator can use SQL Server's backup facility to dump the contents of the transaction log, the database, or both, while SQL Server remains running. This type of backup is called dynamic backup or on-line backup. SQL Server backup operation is referred to as a dump, and the restore operation is referred to as a load. Choosing the correct backup and recovery strategy involves a careful analysis of your environment. Some business critical environments can accept only one strategy: on-line backup and recovery. Others can employ either one. Below we present some of the major characteristics of each strategy, and in the rest of the document we provide more detailed information, especially in the area of performance.6 Shutting SQL Server down for archiving purposes has the following characteristics: Any and all user databases remain inaccessible for the duration of the backup operation. SQL Server opens all databases at startup and keeps them open whether they are being accessed or not. Therefore, you cannot use typical file backup utilities while SQL Server is running. 6 This document will provide a performance analysis of online backups only. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797