About SQL Server database relay storage device Analysis

Add the dump device to the location where SQL Server backs up the database. In the SEM the storage equipment is visible, and the information on the device is stored in the Sysdevice table of the primary database. SysAdmin and Diskadmin server members are allowed to join and undo this device.

The following script shows how to join this device. The Sysdvices table gets the information on the device and then undoes 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

You can seniority the database directly without passing the dump device. The following command completes this step:

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

SEM is not visible in the backup of the database, it is directly to the disk. Therefore, you can add a dump device to point to this file. When the dump device is added, you can use SEM to provide visibility files to the dump device. Adding a dump device does not see the contents of the stored file, but it can be seen in SEM.

　　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 you want to undo this device and file, you can use the Sp_dropdevice command for the logical device name. If you want to delete the file that the dump device points to, you can add the delfiled statement. The following script will undo the previously created device:

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