Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My Experiments with SQLServer
Browse by Tags · View All
SQLServer 126
SQLServer 2008 R2 91
SQLServer 2008 86
SQLServer 2005 82
SQL 60
Database 59
Sql And Me 57
Tips & Tricks 28
SQL Server 27
SQL FAQ 26

Archive · View All
May 2011 25
June 2011 21
July 2011 21
August 2011 19
April 2011 16
January 2013 4
May 2012 3
April 2012 3
October 2011 3
February 2013 2

Vishal Gajjar's Blog

SQL Server – Creating and using Dump Devices for Backups

Sep 26 2011 12:00AM by Vishal Gajjar   

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


Republished from Sql&Me [31 clicks].  Read the original version here [2 clicks].

Vishal Gajjar
46 · 4% · 1276
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]