Database backup

[jvande]

I use the following command to copy my database to another location as a backup.
copy d:\mssql2000\sqldata\backup\2002-Current.bak \\dbserver2\d$\mssql2000\sqldata\backup\2002-current.bak

I would like to implement a daily back routine instead of always overwriting 2002-current.bak. Does anybody know how I could name it the current day and then I can have a weeks backup. For example 2002-Current-Monday.bak and so on....
Thanks,
Josh

 

[SQLBill]

if you are doing it as a scheduled job in Enterprise Manager, you could 'cheat' and make seven jobs. Name each job by the day of the week and on the schedule tab, just schedule it for that day.

-SQLBill

 

[jvande]

I know I can do that but, I thought maybe there would be a way to put in some variable similiar to a timestamp.

 

[rksingh024]

May be following code can help you...
-----------
declare @db varchar(30),
@disk varchar(256),
@weekname varchar(15)

select @db = db_name() ,
@weekname = DATENAME(dw, getdate()) ,
@disk = '\\pc-name\' + @db + '-' + @weekname + '.BAK'

backup database @db to disk = @disk
with init, stats
-----------
Regards,
Ramesh Singh

 

[jvande]

Thanks Ramesh,
Worked out great.