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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 11 On-line archiving allows for incremental backups (transaction log dumps) and partial backups (dumps of selected databases). On-line dumps can take advantage of hardware data compression, if one is available via the tape drive. Software data compression is not, however, available. Since on-line dumps allow backups while the database is active, it would not be efficient to perform a verify operation after the backup. Therefore, the verify operation is not supported. SQL Server, however, reports any errors it encounters via the error log and the event log. The major limiting factor of previous versions of SQL Server-based backup was the performance, mainly due to the size of the I/O requests generated, both for the data (disk) volume as well as for the tape device. Improvements have been made to SQL Server 6.x which increase I/O throughput (see the later section entitled Enhancements in SQL Server 6.x), so that the performance of OnLine backup is not much below that of Off-Line methodologies. We will analyze the performance implications of SQL Server-based backups in greater detail later in this document. SQL Server currently does not support the autoloading and/or cataloging capabilities of the Compaq TurboDAT AutoLoader or DLT Library. However, third party software that supports these products along with a database 'backup agent' for SQL Server can be used. The scope of this paper is limited to describing backup functionality available either directly from SQL Server or from a third party utility to be used in conjunction with SQL Server functionality. Thus, later sections of this document will cover the subject of online backups only, and information on offline backup software and methodologes will be addressed in other documents. Database dumps create complete images of both the database and the transaction log, take more time to perform as opposed to transaction log dumps, and consume more of the backup media. A recent database dump, however, may provide a faster data recovery, if one is needed, as opposed to loading an older database dump and a number of transaction log dumps created afterwards. To illustrate this point, suppose that you create a database dump on Sunday, and each weekday you perform a transaction log dump. If you experience a failure on Saturday, you have to restore the database image created on Sunday and five transaction log dumps created during the week. However, if you create a database dump on Sunday and again on Wednesday, with transaction log dumps on the days in between, to recover from the failure on Saturday you would have to restore the database image created on Wednesday and follow it with restores of only two transaction log dumps, one from Thursday and one from Friday. Transaction log dumps8, or on-line incremental backups, create only images of the transaction log, take substantially less time to perform, and consume only a fraction of the backup media as compared to database dumps. A high number of transaction log dumps since the last database dump will prolong your data recovery, as illustrated in the example above. The strategy involving these types of backup and their frequencies depends entirely upon your requirements. A careful analysis of your needs, along with techniques outlined in the System Administrator's Guide, will help you determine a plan that is best for you. You have many options for storing your backup images. You can back up your data to a local tape drive, a hard disk drive or a local rewrittable optical disk drive. Or you can back up to a remote server, which can store the data on its tape drive, hard disk drive or rewrittable optical disk drive. This section will explain the advantages and disadvantages of each type of storage destination. 8 Transaction log dump requires the transaction log to reside on its own device file. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797