Syntax Error Help

• Taylor Prentice

  SSC Veteran

  Points: 269

  More actions

  April 18, 2007 at 8:12 am

  #118186

  hello,

  i'm trying to script a job to back up a database everynight and create a new filename based on the date. I'm getting syntax error near the '+'.

  declare @date as varchar(255)

  set @date = convert(varchar,getdate(),112)

  EXEC sp_addumpdevice 'disk', 'mydatabasebackup', 'G:\MS SQL\Backups\mydatabase\mydatabase_backup_'+@date+'.bak'

  BACKUP DATABASE mydatabase TO mydatabasebackup

  I also tried this without creating the variable and just using convert(varchar,getdate(),112) in the device path with same problem.  why can't I string this together this way?

  I can't use the maintenance plan, because I have to take the database to single user mode and back in this process, so a scripted job is best.  I have the rest of the job working, and need to figure this out to really polish it off.

  thanks in advance for any advice!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 18, 2007 at 8:18 am

  #701132

  Have you tried something like this?

  declare @date as varchar(255)

  set @date = convert(varchar,getdate(),112)

  declare @Path as varchar(255)

  set @Path = 'G:\MS SQL\Backups\mydatabase\mydatabase_backup_'+@date+'.bak'

  EXEC sp_addumpdevice 'disk', 'mydatabasebackup', @Path

  BACKUP DATABASE mydatabase TO mydatabasebackup