Presents your 
SQL SERVER E-NEWSLETTER for June 20, 2002
--------------------------------------------

DUMP DEVICES OFFER VISIBILITY TO DATABASE BACKUPS

Adding a dump device allows SQL Server access to the location in which
database backups are stored. The dump devices are visible through SQL
Server Enterprise Manager (SEM) and the information on the device is stored
in the sysdevices table in the master database. Members of the sysadmin
and diskadmin server roles have permissions to add and drop devices.

The following is a sample script that demonstrates how to add the
device. The sysdevices table is queried for information on the device and then
the dump device is dropped.

EXEC sp_addumpdevice 'DISK', 'pubs_dump', 'c:\pubs_dump.bak'
GO
BACKUP DATABASE pubs TO pubs_dump WITH NOINIT, STATS = 10
GO
SELECT name logical_name, phyname physical_name, * FROM master..sysdevices WHERE name = 'pubs_dump'
GO
EXEC sp_dropdevice pubs_dump, DELFILE
GO

You can back up a database directly to disk without a dump device. The
following command will accomplish this:

BACKUP DATABASE pubs TO DISK='c:\pubs_filedump.bak' WITH NOINIT, STATS = 10
GO

SEM is unable to see the database backup that went directly to disk.
Therefore, you can add a dump device that points to the file. When the dump
device is added, you can use SEM to provide visibility to the dump
device. The contents of the backup file aren't touched when adding the dump
device that points to the file, and SEM is able to see the contents of thefile.

SELECT name logical_name, phyname physical_name, * FROM master..sysdevices WHERE name like '%pubs%'
GO
EXEC sp_addumpdevice 'DISK', 'pubs_diskdump', 'c:\pubs_filedump.bak'
GO
SELECT name logical_name, phyname physical_name, * FROM master..sysdevices WHERE name like '%pubs%'
GO

To drop the devices and files, you can issue the sp_dropdevice command
with the logical device name. To delete the files that the dump device
points to, you can add the DELFILE argument. The following script will drop
the devices previously created:

EXEC sp_dropdevice pubs_diskdump, DELFILE
GO
SELECT name logical_name, phyname physical_name, * FROM master..sysdevices WHERE name like '%pubs%'
GO
----------------------------------------