Backups from SQLcmd ?

I am working with SQL Express and found myself surprised when I tried to automate a backup... No Agent.

So I am trying to create a simple backup script to run from the command line and probably schedule through the scheduled task manager.

here is my problem. I get the following error :

Msg 2812, Level 16, State 62, Server FIREFLY\SQLEXPRESS, Line 1
Could not find stored procedure 'B'.

Here is the batch file

sqlcmd -i c:\temp\test\DBbackup.sql -o c:\temp\test\output.txt -S FIREFLY\SQLEXPRESS

And here is the sql input file :

BACKUP DATABASE [DNNDEV] TO DISK = 'C:\temp\test\dnndev.bak' WITH NOFORMAT, NOINIT, NAME = 'dnndev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

If I put a USE statement in front of my backup statement - the error message changes to

Could not find stored procedure 'U'.

So it would appear to list the first character encountered.

As a sanity check I created a similar process which does a select * from a table and I do not get an error saying :Could not find stored procedure 'S'. - instead all is well and I get my output and no error

If I try the backup command in SQLCMD interactively it works.

so what the heck am I missing ?

Thank you for any help...

What editor did you use to save the SQL file? Sounds like you generated a unicode file without BOM (byte order mark) so it is treated as ANSI file. Check the save options of your editor. Notepad for example has a combo box in its save dialog where you should choose ANSI. Unicode will save 2 or more byte per character. For standard letters it is often the ANSI code followed by a 0 byte, which is a terminator for ASCII c strings - so I guess sqlcmd stops after hitting the first 0 byte which follows your first letter.

--
SvenC

|||

Yep - That did it. Somehow my other test using the select statement must have been saved in ANSI - my backup file was saved in unicode

Thanks a million !