Backup Database Script

• thinknight

  SSCrazy

  Points: 2068

  More actions

  December 17, 2008 at 8:51 am

  #204198

  Hi,

  declare @today varchar(3),@backupString varchar(255)

  select @today=

  CASE DatePart(dw,getDate())

  WHEN '1' THEN 'SUN'

  WHEN '2' THEN 'MON'

  WHEN '3' THEN 'TUE'

  WHEN '4' THEN 'WED'

  WHEN '5' THEN 'THU'

  WHEN '6' THEN 'FRI'

  ELSE 'SAT'

  END

  set @backupString = 'BACKUP DATABASE Test TO '

  set @backupString = @backupString + 'DISK = N''D:\BackupDB\Test'+@today+'_Backup.bak'' WITH INIT , '

  set @backupString = @backupString + 'NOUNLOAD , '

  set @backupString = @backupString + 'NOSKIP , '

  set @backupString = @backupString + 'STATS = 10, NOFORMAT '

  print @backupString

  exec @backupString

  Here is the script to backup Test database to destination folder. But I want to rename backup file with day of week.

  But it does not work.

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  December 17, 2008 at 9:52 am

  #913983

  Try changing the exeucte to

  EXEC sp_executesql @backupString

  and using an NVARCHAR on the backupstring. I tested your code several times that way. It worked finel.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• thinknight

  SSCrazy

  Points: 2068

  More actions

  December 17, 2008 at 10:48 am

  #914038

  Thanks a lot,

  it works fine.