A dump device is simply a logical device which redirects to a specified physical device. The main advantage of using a dump device for backups is it reduces the change required to backup code. For example, if you are backing up to a Network location. If you have hard-coded the backup location then the network location change will require a code change as well.
You can create a dump device using SSMS or T-SQL.
To create a dump device using SSMS:
1. Expand "Server Objects" in Object Explorer,
2. Right Click on "Backup Devices" and choose "New Backup Device"
3. Provide a logical Device name and the physical backup file location for dump device
4. Click "OK" to create the dump device.
To create a dump device using T-SQL:
To create a dump device using T-SQL you can use system stored procedure sp_addumpdevice.
USE [master]
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'MyBackups',
@physicalname = N'C:\Database\Backup\BackupDisk.bak'
GO
Result Set:
(1 row(s) affected)
View all dump devices:
You can get the list of existing dump devices on server using sys.backup_devices catalog view:
SELECT *
FROM sys.backup_devices
GO
Result Set:
Name type type_desc physical_name
———– —– ———– ——————————–
MyBackups 2 DISK C:\Database\Backup\BackupDisk.bak
(1 row(s) affected)
Using the dump device for BACKUP:
Once the dump device is created you can use it to store backups, The dump device will be listed in "Select Backup Destination" dialog box:
You can use it in T-SQL as below:
/*
BACKUP DATABASE [SqlAndMe]
TO DISK = N'C:\Database\Backup\SqlAndMeBackup.bak'
WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
*/
BACKUP DATABASE [SqlAndMe]
TO [MyBackups]
WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Result Set:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 44680 pages for database 'SqlAndMe', file 'SqlAndMe' on file 2.
100 percent processed.
Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 2.
BACKUP DATABASE successfully processed 44681 pages in 23.542 seconds (14.827 MB/sec).
Changing Physical File location for dump devices:
To change the physical file location for dump device, you need to drop and recreate the dump device. If you need to move the current physical file to a new location, it can be done using Windows Explorer or any other File Manager. The physical file is not locked unless a BACKUP/RESTORE is in progress.
USE [master]
GO
EXEC master.dbo.sp_dropdevice
@logicalname = N'MyBackups'
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'MyBackups',
@physicalname = N'C:\NewLocation\BackupDisk.bak'
GO
SELECT *
FROM sys.backup_devices
GO
Result Set:
Device dropped.
Name type type_desc physical_name
———— —— ———— ———————–
MyBackups 2 DISK C:\NewLocation\BackupDisk.bak
(1 row(s) affected)
Hope This Helps! Cheers!
Reference : Vishal (http://SqlAndMe.com)
Filed under: Backup & Recovery, Catalog Views, Management Studio, SQLServer 
