Backup Devices

Three types of backup devices are defined by SQL Server 2005: disks, named pipes, and tape.

Disk Devices

The disk device is not a backup device in the same sense that a tape drive is. It is really a location, a file, on a hard disk or storage area network to place a backup file, which is the same object that gets stored on a tape cartridge. I am not sure why Microsoft called the backup file a disk device, but it is an unfortunate choice of nouns. (Windows Server 2003 Backup calls it a file. A file is a file in my book, so just don’t go looking for any disk devices when you really need to be supplying the name of a file.)

You can back up to a file on any remote hard disk or other device (such as optical storage) on the network. All you have to do in the backup script or in Management Studio is define a UNC path to the drive of your choice. Naturally, SQL Server will need permission to access the destination through share points and the appropriate permissions.

Why would you want to back up to a remote disk, and plop a huge backup file on it? For starters, you might want to back up that file to an external backup library (discussed next) because SQL Server backup only works on locally addressed devices (they have to be physically attached to the server). Naturally you need your brain scanned if you back up to the same disk on which your database filegroup resides. If you lose the disk, you’re history.

Tape Devices

Tape devices are the safest and fastest means of backing up SQL Server databases; however, there are few limitations.

The tape device you are using must be physically connected to the database server. This is a limitation that I think drives a number of DBAs back out to third-party vendors for their backup solutions because data centers typically prefer a centralized backup solution, using a robotic tape library And if you scale out to multiple servers, every server will have to have a tape device. If you are using high-end devices like DSS or DLT, you could easily throw a few thousand dollars or more at each server. In such a scenario, if you still want to use SQL Server Backup, you will have to back up to the file “device” and then sweep that file up into an external tape drive. The only limitation I see with such a solution is that to restore the database, you first have to restore the file to the location you originally backed it up to (which will cost some time).

The backup functionality does not integrate with Windows Server 2003 Removable Storage, so you cannot program in operator alerts and other helpful bells and whistles like media management.

Later in the chapter, I will discuss backup bandwidth and suitable backup tape formats supported on Windows 2000 Server or Windows Server 2003.

Device Names

A backup device name is the “handle” SQL Server assigns to a logical or physical backup device. The device can either be a file on the disk, an actual tape drive, or some means of storing data. The device name is an alias or nickname that you can use to reference in your backup scripts. For example, the code

 BACKUP DATABASE Customers TO DISK='C:\Backups\Modelize\FullBackup.Bak'

backs up the Customers database to the FullBackup file device, named Path\ FullBackup.