Microsoft KB Archive/250337

From BetaArchive Wiki
Knowledge Base

INF: Appending Backups to Tape May be Slower in SQL Server 7.0 than SQL Server 6.5

Article ID: 250337

Article Last Modified on 3/3/2000


  • Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q250337


Performance of a backup to tape may be slower in SQL Server 7.0 than in SQL Server 6.5. This problem is apparent when backups are appended to tape and the tape contains numerous backup sets. The performance degradation becomes more noticeable as the number of backups on the tape grows.


If most of the backups are under 100 MB in size, the performance degradation may not be apparent.

The performance difference is due to a change in the backup algorithm used in SQL Server 7.0. In SQL Server 7.0, the backup rewinds the tape and then searches for each beginning/ending filemark that separates each backup set. This is to ensure the backups are properly appended to tape. In SQL Server 6.5, the backup went to the end of the last backup set without reading the filemarks, which is not as reliable as the method implemented in SQL Server 7.0.

Alternative backup strategies include:

  • Back up to disk and then copy the backup files to tape.
  • Back up the databases according to size and back up the smaller databases first.
  • Use multiple tapes or tape drives so that fewer databases are backed up per tape.

Additional query words: backup; tape; append; performance

Keywords: kbinfo KB250337