Batch file syntax for sqlcmd

Question

Hi All: in server 2008 R2, I have a simple sqlcmd to back up my sqlexpress dBs that runs properly from the command line:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" /S .\SQLEXPRESS /E /Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'"

I'm trying to convert this to a batch script so that I can run it using task scheduler, but it seems like I need to escape certain characters or I get """ is not recognized as an internal or external command..."

I tried escaping the quote marks, like this:

^"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd^" /S .\SQLEXPRESS /E /Q ^"EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'^"

but when I test running the batch, cmd.exe just consumes 100% of CPU and doesn't seem to execute the actual commands. Is there any way to debug the batch file so I know what the proper syntax should be?

Answer 1

Is there any way to debug the batch file so I know what the proper syntax should be?

Hello,

You can use SSMS (SQL Server Management Studio) for this, in menu "Query" you can switch to SQLCMD mode to test your script.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Thanks Olaf, but I already know the sql command works properly...I can execute it from the command line without a problem.

The problem is converting it to a batch file so I can schedule it to run using Task Scheduler. I can't get the batch file to run without compile errors, and task scheduler's error messages aren't informative.

Answer 3

Does the folder named 'c:\tempStuff\' exist ?

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 4

Yes...I can run this sql command from a command prompt without a problem. It's only when I try to run it as a batch file that I get the error.

In other words: this works properly from the command line:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" /S .\SQLEXPRESS /E /Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F"

but if I try to save this command as a batch file, and run the batch file, like:

c:\tempStuff\sqlbackup.bat

I receive errors like  """ is not recognized as an internal or external command..."

So I tried escaping the quote marks with an escape character (see my first post in this thread), but I'm still getting errors parsing the batch file. 

Answer 5

Go through the given link, may be its helpful.

http://geekepisodes.com/sqlbi/2011/executing-the-script-in-batch-file-using-sqlcmd-command/

Answer 6

Hmmm...I feel like I've tried that already, but I went back and verified that my sqlcmd executed properly from the command line:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" -E -S ".\SQLEXPRESS" -Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'"

I then saved it as a BAT file, and if I try to run the BAT from the command line, I get the following error:

c:\tempStuff>sqlbackupcore.bat

c:\tempStuff>■"
'■"' is not recognized as an internal or external command,
operable program or batch file.

This is the same error I've been struggling with all along.

Answer 7

Hmmm...I feel like I've tried that already, but I went back and verified that my sqlcmd executed properly from the command line:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" -E -S ".\SQLEXPRESS" -Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'"

I then saved it as a BAT file, and if I try to run the BAT from the command line, I get the following error:

some characters do have special semantics in command shell.

Why do you not write the T-SQL statement into a file and just pass the file to sqlcmd using -i option?

the file (test.ql) containing the T-SQL statement would look like:

EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'

and your batch file would look like:

C:\"Program Files\Microsoft SQL Server"\100\Tools\Binn\sqlcmd.exe -E -S ".\SQLEXPRESS" -i test.sql -n

you can generate the file test.sql from within the batch file before the call of sqlcmd.exe or just create it hardcode. you've probably to specify the location of test.sql in the batch file.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 8

Hi Daniel: Thanks for your help. I followed your suggestion and created a separate sql script, and then a new BAT file called "sqlbackupNew.bat" 

(BTW: I'm using Notepad to create/edit the files)

The BAT file has a single command in it:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -E -S ".\SQLEXPRESS" -i sqlbackup.sql -n

And if I try to run the BAT from the command line, I still am getting errors:

c:\tempStuff>sqlbackupNew.bat

c:\tempStuff>■"
'■"' is not recognized as an internal or external command,
operable program or batch file.

Answer 9

please put at the beginning of the batch file the following command:

echo on

you will then see if sqlcmd is executed before the error or not.

could you try to start the batch file with complete batch: c:\tempStuff\sqlbackupnew.bat

and please put the double quote around sqlcmd.exe as show in my sample.

To check proper setup of your batch environment, create an batch file with "echo" as the only line (without double quote) and try to execute it.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 10

Could you put a screenshot of this error message?

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

Answer 11

Could you put a screenshot of this error message?

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

Answer 12

please put at the beginning of the batch file the following command:

echo on

you will then see if sqlcmd is executed before the error or not.

could you try to start the batch file with complete batch: c:\tempStuff\sqlbackupnew.bat

and please put the double quote around sqlcmd.exe as show in my sample.

To check proper setup of your batch environment, create an batch file with "echo" as the only line (without double quote) and try to execute it.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

OK...I amended the script as you suggested...I didn't notice how you had quoted only the part of the command line with spaces in it...so my BAT file now looks like this:

echo on
C:\"Program Files\Microsoft SQL Server"\100\Tools\Binn\sqlcmd.exe -E -S ".\SQLEXPRESS" -i c:\tempstuff\sqlbackup.sql -n

That results in a similar error:

c:\tempStuff>sqlbackupNew.bat

c:\tempStuff>■e
'■e' is not recognized as an internal or external command,
operable program or batch file.

I also tried executing the BAT using the full path, but I get the same error.

Answer 13

It seems there is a special character in the commands in the batch file.

Could you retype (not copy/paste) the commands in a new batch file? You can forget about using an escape character for a single or double quote. Quotes work just fine in my testing.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

Answer 14

It seems there is a special character in the commands in the batch file.

Could you retype (not copy/paste) the commands in a new batch file? You can forget about using an escape character for a single or double quote. Quotes work just fine in my testing.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

aaargh...it seems like pasting in the line was the problem all along. :(

After retyping the command string manually, I finally got it to work. Thanks for following through on this.

Answer 15

The squares in the screenshot gave it away. Squares (or gibberish characters) like that in a command prompt window indicate a special character e.g. tab, new line, carriage return etc.

Although I could see that in the error output that you've been pasting in this thread but I just wanted to be sure that they were actualy coming from the error output and not just while pasting that text here. So asked for a screenshot.

Thanks for marking the reply as answer! Have a great day!

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

Answer 16

Aalam,

Massive thanks! Been going round in circles on this - I cannot believe that all I had to do was retype the command rather than paste!

Answer 17

Met the same issue. The problem was that I saved bat file as UNICODE. When I resaved this file as ANSI - command started to work fine