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 !

backup to device

I am using a device to backup my user database for both data and the transaction log. I want to automate this process and I understand the syntax for the backup of log and data. I want to know what the syntax is for having the complete backup overwrite the existing backup in the device after a number of backups have occured. I can't find anything in the users manual for this. I also want to have my transaction log backups be overwritten periodically. With my current setup the backup device grows and grows
For example
after the third data backup I want the first backup to be overwritten so the device only contains that last three backups. I will backup the transaction log 2x per day, and I want to keep that last 6 transaction log backups to be stored on the device and then the oldest transaction log backup in the device will be overwritten.
thanks>after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups.
I don't think this is doable. When you do backup you use WITH INIT or WITH
NOINIT to tell backup to orverwrite or append to the backup device. No way
you can tell it to purge the first backup set (if there are 3 exist) and
append a new backup set to the backup device. Similar to the log backup.
One thing you can do is you have 3 backup devices for each day. Lets say
you have BACKUP1, BACKUP2, BACKUP3. Do a full backup and 2 log backups
(appended) to each backup device every day. Schedule a job to run full
backup and another job to do log backup. Before each backup do an IF..ELSE
to find out what backup device was used the day before so your backup will
know what backup device to use today.
hth,
"Stephen Harris" <anonymous@.discussions.microsoft.com> wrote in message
news:2C185839-AF58-4269-B4B3-EA0335036FB3@.microsoft.com...
> I am using a device to backup my user database for both data and the
transaction log. I want to automate this process and I understand the
syntax for the backup of log and data. I want to know what the syntax is
for having the complete backup overwrite the existing backup in the device
after a number of backups have occured. I can't find anything in the users
manual for this. I also want to have my transaction log backups be
overwritten periodically. With my current setup the backup device grows and
grows.
> For example,
> after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups. I will backup the
transaction log 2x per day, and I want to keep that last 6 transaction log
backups to be stored on the device and then the oldest transaction log
backup in the device will be overwritten.
> thanks