Why is sp_addumpdevice database specific?

Question

Seems like the current way sp_addumpdevice works it is database specific i.e. you need to specify a database backup filename ending in .bak

 

What I would like to do is to declare a dump device just ONCE and then reuse this definition when I run the backup database command for different databases. 

For example I have a single network folder where I want to store all my backups for a particular Sql Server instance; which I would like to setup like this :

EXEC sp_addumpdevice 'disk', 'dev_edrive','\\199.xxx.xxx.xxx\e$\dbbaks'

 

 

Of course the backup database syntax requires that the disk device end with a filename.bak - so this shall not work in reality.

 

I am not sure how tape backups work, but they donot seem to need a filename to be specified  i.e. the BOL example just declares it as \\.\tape0'

 

I am posting this more as a discussion question  to see if others feel the same way I do.

Answer 1

(a) First of dumpdevice is not database specific and no need to add.bak as file extention

(b) You can create a dumpdevice which can contain backups of multiple databases.

 

--Adding a dump device without .Baki

EXEC sp_addumpdevice 'disk', 'dev_edrive','E:\dbbak'

 

-- Taking backup of multiple databases into the same device

Backup database testdb to dev_edrive

Backup database testtrgdb to dev_edrive

 

-- To get the backup sets in the device

RESTORE Headeronly FROM dev_edrive

 

--To get the filelist of a specific db

RESTORE FILELISTONLY FROM dev_edrive

WITH FILE=2

 

 

 

Madhu

 

Answer 2

Madhu,

I tried the following :

1) Setup dump device without the .bak filename specified

 

EXEC sp_addumpdevice 'disk', 'dev_edrive','\\199.xxx.xxx.xxx\e$\dbbaks'

GO

 

2) Ran a command to backup current database :

 

backup database cprg to dev_edrive with init

go

 

results in the following error message

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device 'dev_edrive(\\199.xxx.xxx.xxx\e$\dbbaks)'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

 

But when I specify the filename in sp_adddumpdevice  like this

EXEC sp_addumpdevice 'disk', 'dev_edrive_cprg','\\199.xxx.xxx.xxx\e$\dbbaks\cprg_dev.bak'

backup database cprg to dev_edrive_cprg with init

go

 

it works just fine