T-SQL - Scripting variable not defined

Question

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

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

The problem is I have a line like this: ...TO DISK = "FileName_$(ESCAPE_NONE(DATE)).BAK" ... With a date in a filename, it will prevent it from replacing my old backups.

If I run it in management studio, it works, but if I run it in command line with the sqlcmd -i command, then it doesn't work.

EDIT:

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

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

I don't quite understand what that means. I've already used $ESCAPE_NONE(DATE), what's wrong?

Deadly-Bagel · Accepted Answer · 2014-12-24 11:27:29Z

2

Old question I know but this is one of the first results and if anyone else has the same problem the answer isn't particularly easy to find.

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

sqlcmd -x -i inputfile

answered Dec 24, 2014 at 11:27

Deadly-Bagel

1,6201 gold badge10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gaspa79 · Accepted Answer · 2012-10-18 12:29:31Z

If you're trying to backup your sql server databases and append the date to them using sqlcmd there's an easy thing you can try.

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

You can invoke it from sql cmd using some command like this:

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

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

The date will be automatically appended and you can play with the sp's code if you want it in a different place/way/format. I use this regularly on servers which don't have a non-express sqlserver (meaning that I can't schedule backups without using a .bat and task scheduler) with great success.

I apologize if this wasn't the answer you were looking for =). Have a nice day!

TulsaDavid · Accepted Answer · 2020-05-18 15:21:48Z

0

I know I'm coming along late on this thread, but you can use the following:

SQLCMD -S YourServer -E -d YourDatabase -i YourScript 2> nul

That will send the StdErrorOut to the bit bucket.

answered May 18, 2020 at 15:21

TulsaDavid

262 bronze badges

Collectives™ on Stack Overflow

T-SQL - Scripting variable not defined

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related