backup job not working

Question

Post reply

backup job not working

Denise McMillan

SSCertifiable

Points: 6216
More actions
July 22, 2005 at 10:11 am

#167900

Can anyone tell me why this backup job doesn't work?
BACKUP DATABASE CE10 to disk = 'c:\mssql7\BACKUP'
WITH NOINIT
The error I receive is
BackupDiskFile::CreateMedia: Backup device 'c:\mssql7\BACKUP' failed to create. Operating system error = 5(Access is denied.).
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Osoba SSCommitted Points: 1964 More actions · Answer 1

Try this:

exec sp_adddumpdevice 'disk','CE10_db',

'c:\location\CE10_db.bak'

Backup Database CE10 to CE10_db

then drop the device when finished

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

I tried adding the name of the backup file and that works:

BACKUP DATABASE CE10 to disk = 'c:\mssql7\BACKUP\ce10_db_0722051200PM'

WITH NOINIT

But how can I get the date and time to be whatever the current date/time is for each days' backup?

Thanks again!

Osoba SSCommitted Points: 1964 More actions · Answer 3

You may want to play with the naming a little but this is how I've done it a few times:

declare @label varchar(16)

set @label=(select convert(varchar,getdate(),112)+convert(varchar,getdate(),108))

declare @sqlscript varchar(2000)

set @sqlscript='exec sp_addumpdevice ''disk'',''DB_BKP'',''C:\DB_BKP_'+@label+'.bak''

backup database 'name' to DB_BKP'

exec (@sqlscript)

Don't forget to drop the device after use and rebuild so you have a new label.

..hope that gives you a good start

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 4

Here's some code from a backup utility I wrote:

DECLARE @FileSaveDate varchar(16)

<...>

-- Get and format the date and time to be used in this backup's file name. Note that

-- converting to varchar(16) chops off the seconds, and the subsequent lines just drop

-- the formatting characters.

SET @FileSaveDate = convert(varchar(16), getdate(), 120)

SET @FileSaveDate = replace(@FileSaveDate, '-', '')

SET @FileSaveDate = replace(@FileSaveDate, ':', '')

SET @FileSaveDate = replace(@FileSaveDate, ' ', '')

This emulates the naming convention you'd get from using the database maintenance plans.

Philip

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 5

How would it go in the backup statement then:

DECLARE @FileSaveDate varchar(16)

-- Get and format the date and time to be used in this backup's file name. Note that

-- converting to varchar(16) chops off the seconds, and the subsequent lines just drop

-- the formatting characters.

SET @FileSaveDate = convert(varchar(16), getdate(), 120)

SET @FileSaveDate = replace(@FileSaveDate, '-', '')

SET @FileSaveDate = replace(@FileSaveDate, ':', '')

SET @FileSaveDate = replace(@FileSaveDate, ' ', '')

BACKUP DATABASE CE10 to disk = 'c:\mssql7\BACKUP\ce10_db_@FileSaveDate.BAK'

WITH NOINIT

Like this?

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 6

I won't be near my source code until Monday, but rather than keep you hanging I'll outline what I do.

The @FileSaveDate value ends up containing a date/time string like "200507221016", and I use this kind string all over the place. For a backup, I build the backup file file name something like this:

<targetFolder>\<dbName>_<backupType>_<date/timeString>.<extension>.

For example, a complete backup of database "ce10" to "C:\mssql7\BACKUP" would look something like:

C:\mssql7\BACKUP\ce10_db_2005072231016.bak

Now, to achive what I think you're goal is, you'd need something like:

DECLARE

@BackupFile varchar(500)

,@FileSaveDate varchar(16)

SET @FileSaveDate = convert(varchar(16), getdate(), 120)

SET @FileSaveDate = replace(@FileSaveDate, '-', '')

SET @FileSaveDate = replace(@FileSaveDate, ':', '')

SET @FileSaveDate = replace(@FileSaveDate, ' ', '')

SET @BackupFile = 'C:\mssql7\BACKUP\ce10_db_' + @FileSaveDate + '.bak'

BACKUP DATABASE CE10 to disk = @BackupFile WITH NOINIT

(I don't recall why, but I think "WITH INIT" might serve you better.)

Why so long-winded? Flexibility. Drive everything with parameters, build the string dynamically, then you call a routine with simple values for the database to back up, where to back it up, and a code for what kind of backup to make (complete, differential, or transaction). And then, joy of joys, you never have to struggle to remember how to get that bothersome BACKUP command to work again.

Philip