Using Multiple Media or Devices
Multiple backup devices can be used for backup and restore operations. This allows Microsoft® SQL Server™ to use parallel I/O to increase the speed of backup and restore operations because each backup device can be written to or read from at the same time as other backup devices. For enterprises with large databases, using many backup devices can greatly reduce the time taken for backup and restore operations. SQL Server supports a maximum of 64 backup devices for a single backup operation.
However, all backup devices used in a single backup (and consequently restore) operation must be of the same type (disk or tape). For example, to back up the sales_db database daily using database and differential database backups to tape, only multiple tape drives can be used.
Note Tape backup devices must be attached to the server physically. It is not possible to use tape backup devices on remote computers.
Creating and restoring backups using multiple backup devices is the same as creating and restoring backups using a single device. The only difference is that all backup devices involved in the operation, not just one, are specified. For example, if a database backup is to be created using three tape backup devices such as \\.\TAPE0, \\.\TAPE1, and \\.\TAPE2, each of the tape devices needs to be specified as part of the backup operation, although fewer tape backup devices can be used when restoring the backup later.
When creating a backup using multiple backup devices on removable media, each backup media does not need to be the same size, have the same amount of storage available, or operate at the same speed. If one backup media used by a backup device runs out of space while a backup is being created, SQL Server stops writing to the backup device and prompts for new media to continue writing to that backup device. While waiting for new media to be inserted into the backup device, the backup operation continues writing data to any other backup devices involved in the backup operation, as long as the backup media used by these devices has space available.
For example, three tape backup devices of equal speed are used to store a database backup. The first two tape media are 10 gigabytes (GB) in size, but the third is only 5 GB in size. If the sales database, which is 20 GB in size, is backed up to all three tape backup devices simultaneously, the backup operation will stop writing to the third backup device and prompt for a new tape when 5 GB has been written to the tape. However, the backup operation continues writing data to the other two backup devices. When the tape media on the third backup device is replaced with a new tape, the backup operation continues writing data to the third backup device.
Several internal synchronization points occur when a database backup is written to multiple backup devices. The most important synchronization point occurs when all the data in the database has been backed up, and the transaction log is about to be backed up. All backup devices used in the backup operation must not be blocked during these synchronization points; otherwise, the entire backup operation is blocked until all backup media is available. For example, three tape backup devices are used to store a database backup, and the second tape backup device is blocked, waiting for the existing tape to be replaced because the space on the tape has been exhausted. If a synchronization point occurs, the entire backup operation will stop until the tape in the second backup device is replaced.
Important When using multiple backup devices to perform backup operations, the backup media involved can be used only for SQL Server backup operations. For more information, see Using Backup Media.