Compaq ProSignia 200 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 12
Compaq Backup and Recovery for Microsoft SQL Server 6.x, sp_con
View all Compaq ProSignia 200 manuals
Add to My Manuals
Save this manual to your list of manuals |
Page 12 highlights
Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 12 Local backup consists of archiving your data to a storage device physically attached to the database server. This device can include a tape drive, a hard disk drive or a rewritable optical disk drive. You can also use a separate disk drive for temporary storage, and move the image to a tape at some later time. Remote backup consists of archiving your data to a storage device located on another server. This device can consist of a hard disk drive or a rewritable optical disk drive. SQL Server does not support direct dumps to a remote tape, even though you can dump your data to a remote server's shared disk drive and then move it to a tape local to that server. The remote server can then act as a database backup server, to which multiple database servers dump their data. With a local backup implementation, each database server requires its own backup storage. This storage is typically a tape drive, even though it may also be a separate disk volume9. There is no additional network overhead generated, but users of this server will experience an increased response time due to the additional workload induced by the backup activity. With a remote backup implementation, the cost of the backup server hardware and software can be shared among many database (and other types of) servers. You may, in some cases, even utilize an existing file server as your database backup server. Additional overhead generated by increased network activity can be alleviated by scheduling backups for after-hours, or by dedicating a separate, high-speed network link between the database server and the backup server. A SQL Server logical dump device can point to either a tape drive (e.g.: \\.\Tape0) or to a file on a disk partition (e.g.: D:\Dump1.DAT). Backups done directly to a tape device are a convenient and relatively inexpensive way of backing up your database. The capacity and the speed are dependent upon the tape hardware. The tape device must be physically attached to the database server to perform SQL Server dumps to tape, unless special database backup agents are used10. File-based tape backup utilities support archiving of remote data, but the backup software must be running on the same machine as the tape drive. Dumps done directly to a disk device, local or remote, are typically faster but more costly as compared to tape backups. You can consider dumping directly to a disk device and then moving the image onto a tape. Frequency of your database and transaction log dumps, scheduling of these dumps and the retention period all depend entirely on your environment. Factors such as an acceptable amount of work that could be lost, if any, acceptable down-time due to a recovery from a failure, the volume of update transactions, etc., will all influence the backup frequency, scheduling, and data retention. SQL Server offers a "media retention" option, configurable through sp_configure, which allows you to prevent overwriting an existing dump too soon. Automatic scheduling is available via the SQL Server Executive service or third party software. Refer to the System Administrator's Guide, documents available from Microsoft, or your third party software documentation for more detailed information. 9 Using the same physical disk volume as your database or transaction log volume is not recommended for two reasons: 1) if your dump destination volume is the same as your database volume, and you lose a disk on this volume, you have no way of recovering your data, and 2) isolating I/O generated by the dump activity from the transaction log and database I/O activity minimizes performance impact. 10 Database Backup Agents are available from Compaq or Cheyenne for use with Cheyenne ARCserve, and are covered later in this paper.