Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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',
...
share|improve this question

1 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'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.