Backup name with DateTime

I know how to backup my DB but i cant find a way to give a name with the current date time.

backup database SUSDB to disk = 'e:\backup\test_%date%.bak' with init;

Any idea how i can put the date to my file name?

thank you

You could use dynamic sq;:

declare

@.sql varchar(300)

set

@.sql='backup database SUSDB to disk = ''e:\backup\test_'+convert(varchar,getdate(),101)+'.bak'''

exec

(@.sql)

|||

You have two options modify the code in the first link for your needs or use the last two links to create one just for you. Hope this helps.

http://support.microsoft.com/kb/241397

Create custom code from these two.

http://forums.asp.net/thread/1049663.aspx

http://msdn2.microsoft.com/en-gb/library/aa259582(SQL.80).aspx

|||

Sorry i forgot to mention that i use my code inside SQL 2005 jobs,

so the backup is taken everynight

|||

So in your job step you should generate file name like was shown in one of previous steps, you can also if you wolud like to keep information how many backup you did in file name, modify backup step automatically on success to conatain current backup number. But for only modifing date, code from first answer should be helpful.

Good luck