Creating Backup Devices with Transact-SQL

To create permanently defined backup devices in T-SQL, use the system stored procedure sp_addumpdevice. The name of this procedure is a throwback to pre–Version 7.0 days, when a backup device was referred to as a dump device, and taking a backup was referred to as taking a dump. I kid you not.

The required syntax is

sp_addumpdevice 'devtype', 'logical_name', 'physical_name' 

Where 'devtype' is one of 'disk' 'tape' or 'pipe', 'logical_name' is the name that will be used to refer to this device, and 'physical_name' is the actual path to the file or tape device.

For example:

To create a disk backup device named NWbackup:

USE master 
EXEC sp_addumpdevice 'disk', 'NWbackup', 'D:\backups\NWbackup.bak'

To ...

Get Microsoft® SQL Server™ 2000 Unleashed, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.