Backup Devices

A backup device is created to provide a storage destination for backups. SQL Server 2000 supports both permanent and temporary backup devices. Permanent backup devices are pre-created, named devices, which can serve as backup devices for multiple backups. Temporary backup devices are specified in the backup statement itself, and are intended for one-time use. Backups are supported to file, tape, and named pipes.

Tape Devices

To state the obvious, tape devices are used to back up to tape. Tape devices must be directly connected to the server, and parallel backups to multiple drives are supported to increase throughput. Tape backups have the advantage of being scalable, portable, and secure. Scalability is important as your database grows. Available disk space often precludes the use of disk backups for large databases. Because tapes are removable media they are easily transported offsite, where they can be secured against theft and damage.

SQL Server supports the Microsoft Tape Format (MTF) for backup devices. This means that SQL Server backups and NT/Windows 2000 backups can share the same tape. This is convenient for small sites with shared use servers and only one tape drive. You can schedule your SQL Server backups and file backups without having to be onsite to change the tape.

Disk Devices

Disk devices consist of a file, generally stored in a folder on a local hard drive. This should not be the same hard drive that your data is stored on! Disk devices have several advantages, including speed and reliability. If you have ever had a backup fail because you forgot to load a tape (come on, admit it), you can appreciate disk backups. On the other hand, if backups are done to a local disk, and the server is destroyed (remember the Scud Test?) you lose your backups as well. Microsoft recognized this issue and SQL Server 2000 allows the use of both mapped network drives and UNC paths in the backup device filename. This allows you to back up to a disk on a remote machine. However, this should be a dedicated or high-speed network connection, and verification of the backup should be done to avoid potential corruption introduced by network error. The time it takes a backup to complete over the network depends on network traffic, so take this into consideration when planning your backups.

NOTE

In a lab test, backing up a 15MB test database to a disk on another server, using a dedicated 10Mbps Ethernet connection, took 47.241 seconds. Compare that to 4.766 seconds backing up to a local disk. This means the database was in backup mode ten times longer when backing up over the network.

Disk or Tape?

So which should you use, disk or tape? Traditionally, backups have been done to tape. Although tape is more secure than disk, the main disadvantage of using tape for backups is that it's slower to back up and restore from tape than from disk. Tapes generally require more "hands on" management to load and unload the appropriate tapes for backups or restores .

In the past, the main disadvantage of using disk storage for backups was it was prohibitively expensive, and still might be for many VLDBs. However, the increased availability of inexpensive, fast, reliable, high capacity disks has changed that, and many sites are now adapting a disk backup strategy. But what about the Scud Test? How do you protect the disk backups?

Here's a strategy that leverages the advantages of both disk and tape backups. Schedule your backups, using SQL Server's Backup utility, to back up your databases to disk devices on the local system. If you need high speed or large storage capacity, set up a RAID 0 array to store the backups on. No fault tolerance you say? That's the next step. After your backups have run, the files on the backup device are "cold" files, they have no physical ties to SQL Server, so the database can now go about its business. The backup has been done in the fastest amount of time with the minimum possible disruption. Now, schedule your network file backup utility to back up these files to its remote storage location. Make sure to leave a buffer time between when your SQL Server backups usually complete, and when the network schedule picks them up. When you can verify the network backup was successful, you can delete the local backups to free up storage, if required. You now have fast, scheduled, reliable backups, with offsite redundancy. And that's a good thing.

TIP

You might want to keep the local backup files on disk around until the next backup is performed in the event you need to recover the database. You can restore and have the database online much faster if the backup files are still on disk, rather than having to restore them from tape first.

Named Pipe Devices

Named pipe devices are included in SQL Server 2000 to support third-party backup tools. Named pipe devices are typically used when backing up to remote tape storage systems. See your vendor's documentation for further information.

NOTE

If you decide to use a SQL Server dynamic backup plug-in for your network backup utility instead of a plan such as the one outlined earlier, make sure you test your restores. There are lots of horror stories out there about un-restorable third party backups. Also, the cost of the plug-in is often more than the cost of a few extra disks.

Multiple Devices

To facilitate support for large databases SQL Server supports backing up to multiple devices. This is referred to as a parallel or striped backup. This can significantly reduce your backup and restore times, as the backup is written to multiple devices simultaneously . When backing up to multiple devices all devices must be the same media type and can't be mixed with another type of backup. After a device is used for a parallel backup, that device is considered part of a backup set, and can't be used for other backups unless they, too, stripe across the same set of devices, or the member is reformatted. If you reformat any member of a backup set the data in the remaining members is unusable. Both permanent and temporary backup devices can be part of a backup set. For example, the following syntax could be used to back up to multiple permanent devices:

 BACKUP DATABASE northwind TO nwback1, nwback2, nwback3