inserting date into sql script

Question

i am doing sql server backups like this in script:

BACKUP DATABASE databasename TO DISK = `path'

since this backup is going to be automated, how do i insert today's date into 'path' ?

Answer 1

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

Answer 2

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