sp_add_jobschedule puzzle for special weekly job....

[DLu]

Hi:
in sp_add_jobschedule
@freq_type = 8 for weekly @freq_interval = 1 for Sunday.

I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak.
and next Sunday's should be backup_DBTest_2003_07_27.bak.
(if this way, the backup device will be created at the begining of the job running...)

this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?

Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....

thanks
David

[skhanal]

Use Database Maintenance Plan Wizard to create a backup job. This will create a backup file tagged with date, you will also have an option to automatically delete backups older than number of days/weeks you choose.

If you don't like this then you have to write a transact sql script to get the date and append to the backup file name and issue backup command.

[MAK]

schedule this for every sunday

declare @path varchar(1000)
set @path = ' backup database model to disk = ' + ' "c:\model' + '_' + convert(varchar(100),year(getdate()) )+'_'+convert(varchar,month(getdate()))+'_'+conver t(varchar,day(getdate()))+ '.bak" with init'
print @path
exec (@path)