Today we will talk about how to add dump device where the SQL Server backup database. Dump device is visible in SEM, and the information on the device can be stored in sysdevice table of the key databases .The member servers Sysadmin and diskadmin can allow this device to be added and undone. The following script will display how to add this device .And then the Sysdvices table will get the information from the device and undo the dump device.
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
BACKUP DATABASE pubs TO DISK=‘c:pubs_filedump.bak’
WITH NOINIT, STATS = 10
GO
SEM is invisible in the backups of Database .So, you can add the dump device to point this file .When the dump device is added, you can use SEM to provide visible files for the dump device. Although the content in the storage files can not be seen when the dump device was added, the SEM can see them.
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
If we want to undo this device and files, you can use the command sp_dropdevice to logic device name .If you want to delete the file the dump device pointed, you can add the Delete statement .The following script will undo the device created previously.