Database backup with T-SQL script - Issue with Declare and Set Variables

Question

Can someone tell me why I can't backup my database with the script below? I can run the script but it didn't create the backup.
I"m using SQL Server 2008

DECLARE @pathName NVARCHAR(512)
SET @pathName = 'C:\Backup\MyBackup.bak'

...

EXEC sp_add_jobstep
...
@command = 'BACKUP DATABASE MyBackup TO DISK = @pathName WITH INIT',
...

Answer 1

Have you looked at http://msdn.microsoft.com/en-us/library/ms187358%28v=sql.100%29.aspx?

It looks like you are trying to insert a local variable into a SQL Server Agent job.

Based on this section:

[ @command= ] 'command'

The commands to be executed by SQLServerAgent service through subsystem. 

command is nvarchar(max), with a default of NULL. SQL Server Agent provides token substitution that gives you the same flexibility that variables provide when you write software programs.

You may want to try using tokens instead of local variables. This article may be a good starting point for you: http://msdn.microsoft.com/en-us/library/ms175575%28v=sql.100%29.aspx

As it points out, your solution will look something like:

@command = 'BACKUP DATABASE MyBackup TO DISK = $(ESCAPE_SQUOTE (@pathName)) WITH INIT'