Backup Devices

When creating backups, you must select a backup device for the data to be backed up to. Microsoft® SQL Server™ 2000 can back up databases, transaction logs, and files to disk and tape devices.

Disk Devices

Disk backup devices are files on hard disks or other disk storage media and are the same as regular operating system files. Referring to a disk backup device is the same as referring to any other operating system file. Disk backup devices can be defined on a local disk of a server or on a remote disk on a shared network resource, and they can be as large or as small as needed. The maximum file size is equivalent to the free disk space available on the disk.

If the backup is to be performed over the network to a disk on a remote computer, use the universal naming convention (UNC) name in the form \\Servername\Sharename\Path\File to specify the location of the file. As with writing files to the local hard disk, the appropriate permissions needed to read or write to the file on the remote disk must be granted to the user account used by SQL Server.

Because backing up data over a network can be subject to network errors, verify the backup operation after completion. For more information, see Verifying Backups.

Important  Backing up to a file on the same physical disk as the database is not recommended. If the disk device containing the database fails, there is no way to recover the database because the backup is located on the same failed disk.

Tape Devices

Tape backup devices are used in the same way as disk devices, with the exception that:

• The tape device must be connected physically to the computer running an instance of SQL Server.

  Backing up to remote tape devices is not supported.

• If a tape backup device is filled during the backup operation, but more data still needs to be written, SQL Server prompts for a new tape and continues the backup operation.

Note  Backups to tape devices cannot be performed on instances of SQL Server 2000 running on Microsoft Windows® 98.

To back up SQL Server (or Microsoft Windows NT® 4.0 or Windows 2000) data to tape, use a tape backup device or tape drive supported by Windows NT 4.0 or Windows 2000. Additionally, use only the recommended tapes for the specific tape drive (as suggested by the drive manufacturer). For more information about installing a tape drive, see the Windows NT 4.0 and Windows 2000 documentation.

Physical and Logical Devices

SQL Server identifies backup devices using either a physical or logical device name.

A physical backup device is the name used by the operating system to identify the backup device, for example, C:\Backups\Accounting\Full.bak.

A logical backup device is an alias, or common name, used to identify the physical backup device. The logical device name is stored permanently in the system tables within SQL Server. The advantage of using a logical backup device is that it can be simpler to refer to than a physical device name. For example, a logical device name could be Accounting_Backup, but the physical device would be C:\Backups\Accounting\Full.bak.

When backing up or restoring a database, you can use either physical or logical backup device names interchangeably.

For example, execute the BACKUP statement with either the logical or physical device name:

-- Specify the logical backup device.
BACKUP DATABASE accounting 
   TO Accounting_Backup
-- Or, specify the physical backup device.
BACKUP DATABASE accounting 
   TO DISK = 'C:\Backups\Accounting\Full.Bak'

To create a logical disk backup device

Transact-SQL