0

I am trying to take a full backup of the database and get an error that I must declare scalar variable. Below is my code; I don't know if I am using variables right in the stored procedure parameters. Any help would be appreciated 

DECLARE @backupfilename VARCHAR(1000)
DECLARE @path VARCHAR(1000)

SET @backupfilename='C:\SqlBackups\AdventureWorks\Full\backup_' + CONVERT (VarChar, GetDate(), 112) + '.bak'
SET @path = 'BACKUP DATABASE AdventureWorks2012 TO  DISK = @backupfilename'

EXEC msdb.dbo.sp_add_jobstep  
    @job_name = 'BackupDatabase',   
    @step_name = 'Weekly Full Backup',   
    @subsystem = 'TSQL',   
    @command = @path;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
simer
  • 47
  • 4

1 Answers1

2

You get an error because your job step command contains string @backupfilename, which is a variable that is not defined in the context of this command. If I am not mistaken sp_add_jobstep does not support a way how you could pass parameters into this command, so you need to create command string without parameters:

set @path ='BACKUP DATABASE AdventureWorks2012 TO  DISK ='''+ @backupfilename + ''''

Here I concatenated the string and also added quotes.

dotnetom
  • 24,551
  • 9
  • 51
  • 54
  • thanks for the help, i was successful in taking the backup but why still didnt understand why are we adding 3 colons before the variable and at the end – simer Oct 07 '18 at 07:12
  • @simerjitsingh I guess you meant three single quotes. In T-SQL quotes are escaped by doubling them (see https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server). So first quote is the escape character, second is the symbol that needs to be in the `@path` variable, and last one is closing of the string. Same thing with four quotes at the end. – dotnetom Oct 07 '18 at 07:23
  • how can i change this script to take differentials and log backup? – simer Oct 07 '18 at 08:11