To create a named disk backup deviceMicrosoft® SQL Server™ backs up databases, transaction logs, and files to backup devices. Backup devices include disk, tape, and named pipe 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 server’s local disk or on a remote disk on a shared network resource, and 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 name (UNC) 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 error, verify the backup operation after completion.
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 backup devices are used in the same way as disk devices, with the exception that:
Backing up to remote tape devices is not supported.
To back up SQL Server (or Microsoft Windows NT®) data to tape, use a tape backup device or tape drive supported by Windows NT. 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 your Windows NT documentation.
Named pipe backup devices allow third-party vendors a flexible and powerful way to connect their own software and provide specialized backup and restoration capabilities. To use a named pipe backup device, you provide as part of a BACKUP or RESTORE Transact-SQL statement the name of the named pipe that will be used by a client application.
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 named disk backup device
To create a named tape backup device
To delete a named backup device
| BACKUP | RESTORE |
| Backup Devices | SQL Server File Permissions |
| Using Multiple Media or Devices |