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 :
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.
(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
-- Taking backup of multiple databases into the same device
Backupdatabase testdb to dev_edrive
Backupdatabase testtrgdb to dev_edrive
-- To get the backup sets in the device
RESTOREHeaderonly FROM dev_edrive
--To get the filelist of a specific db
RESTOREFILELISTONLY FROM dev_edrive
I tried the following :
1) Setup dump device without the .bak filename specified
EXECsp_addumpdevice 'disk', 'dev_edrive','\\199.xxx.xxx.xxx\e$\dbbaks'
2) Ran a command to backup current database :
backupdatabase cprg to dev_edrive with init
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
EXECsp_addumpdevice 'disk', 'dev_edrive_cprg','\\199.xxx.xxx.xxx\e$\dbbaks\cprg_dev.bak'
backup database cprg to dev_edrive_cprg with init
it works just fine