T-SQL - script variable not defined

Question

I am new to T-SQL and I am trying to backup my databases (using SQL Server 2008).

When I try to run the script via sqlcmd -i inputfile , I got this error message: 'DATE' Scripting variable not defined .

The problem is that I have this line: ... TO DISK = "FileName_$(ESCAPE_NONE(DATE)).BAK" ... With a date in the file name, this will prevent the replacement of my old backups.

If I run it in the management studio, it works, but if I run it on the command line using the sqlcmd -i command, then this will not work.

EDIT:

I looked at the job history and I saw this error message:

"For SQL Server 2005 Service Pack 1 or later, you must use the appropriate ESCAPE_xxx macro to update the job steps containing tokens before the job can run."

I do not quite understand what this means. I already used $ESCAPE_NONE(DATE) , what's wrong?

Answer 1

An old question that I know, but this is one of the first results, and if someone else has the same problem, the answer is not very easy to find.

Enabling the -x switch to disable environment variables fixed the problem for me;

 sqlcmd -x -i inputfile 

Answer 2

If you are trying to back up your SQL Server databases and add a date to them using sqlcmd, try to do this easily.

First create sp sp_BackupDabases, which you can find here: http://support.microsoft.com/kb/2019698

You can call it from sql cmd using the following command:

 sqlcmd -U Damieh -P ilovechocolate -S (local) -Q "EXEC sp_BackupDatabases @backupLocation ='C:\MyBackups\', @BackupType='F'" 

I'm sure you already know this, but just in case: -U is the user, -P is the password, -S is the server, and -Q is the request. You can either create a backup of all your databases, or some of them, there are options for this. You can find the saved parameters of the process parameters in the same link that I gave you.

The date will be automatically added, and you can play with the sp code, if you want, elsewhere / in format / format. I use this regularly on servers that don't have non-line sqlserver (which means that I can't schedule backups without using .bat and the task scheduler) with great success.

I apologize if this is not the answer you were looking for =). Have a nice day!