inserting date into sql script

i am doing sql server backups like this in script:

BACKUP DATABASE databasename TO DISK = `path'

since this backup is going to b开发者_运维知识库e automated, how do i insert today's date into 'path' ?

declare @path varchar(255), @mydb varchar(50)
SELECT @mydb = 'MyDBToBackUp'
select @path = 'C:\foo\bar\' + @mydb + '-' + convert(varchar(8),getdate(),112) + '.bak'
BACKUP DATABASE @mydb TO @path

BACKUP DATABASE takes local variables

Edit: last line should be this: Oops. Sorry.

 BACKUP DATABASE @mydb TO DISK =  @path

you can get today's date in yyyymmdd format like this

convert(varchar(8),getdate(),112)

example, change print to exec

declare @date varchar(8)
select @date = convert(varchar(8),getdate(),112)

--change print to exec
print ('BACKUP DATABASE databasename TO DISK = ''path' + @date + '''')

that will generate this statement

BACKUP DATABASE databasename TO DISK = 'path20100714'

You probably also want to add an extension like BAK

also look into INIT and NOINIT, INIT will overwrite the backup if it already exists with that name NOINIT will append, see also: http://msdn.microsoft.com/en-us/library/ms186865.aspx