Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Creating and using Dump Devices for Backups

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"

image

3. Provide a logical Device name and the physical backup file location for dump device

image

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:

image

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.