spooling database osql command error

I am troubleshooting an Ahsay online backup error and was instructed by their tech support to issue the following osql command to see if the database file in question can be spooled successfully. the command issued was:

osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'database_name' BACKUP DATABASE @dbname TO DISK = 'C:\temp\testdump.txt' WITH SKIP"

'datbase_name' is the name of the database

executing this command I get the following error:

Msg 207, Level 16, State 1, Server SERVERNAME Line 1 Invalid column name 'database_name'

the sql server is sql 2005 (free version). Can anyone explain how to correct. I am not too familiar with SQL
Thank you
ITIExperts1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

computerstreberCommented:
You need to change this: database_name to the name of your database.
0
computerstreberCommented:
Here is an example using the AdventureWorks database:
osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'AdventureWorks' BACKUP DATABASE @dbname TO DISK = 'C:\temp\testdump.bak' WITH SKIP"

Open in new window

0
ITIExperts1Author Commented:
I used database_name here just for illustrative purposes. the actual command did contain the database name pgs_core:
osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'pgs_core' BACKUP DATABASE @dbname TO DISK = 'C:\temp\testdump.txt' WITH SKIP"

Msg 207, Level 16, State 1, Server SPGHOST Line 1 Invalid column name 'pgs_core'
 sorry for the confusion.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

computerstreberCommented:
It my experience, the error you received generally occurs when you use, for example, double quotes instead of single quotes. Can you open a command prompt and try this:
C:\SET DBNAME=pgs_core [PRESS ENTER]
C:\OSQL -SSPGHOST -E -dMASTER -Q "BACKUP DATABASE %DBNAME% TO DISK = 'C:\temp\testdump.bak' WITH SKIP"

Open in new window

0
ITIExperts1Author Commented:
I tried your command, output as follows:

Processed 560 pages for database 'pgs_core', file 'tsfp' on file 1.
Processed 1 pages for database 'pgs_core', file 'tsfp_log' on file 1.
BACKUP DATABASE successfully processed 561 pages in 0.446 seconds (10.30
MB/sec).
0
computerstreberCommented:
I can't really explain why, but I think there is either a syntax error in your command or it was failing with the "txt" extension because of size. But the message you got indicates that the backup was successful. If you go to the C:\TEMP directory of the SPGHOST server, you should see a file called testdump.bak. This is the back file.
0
ITIExperts1Author Commented:
i dont know what is going on here. i reissued the command and got the following output:
C:\>osql -E -Q "DECLARE @dbname char(64) SET @dbname = 'pgs_core' BACKUP DATABAS
E @dbname TO DISK = 'C:\temp\testdump.txt' WITH SKIP"

Processed 560 pages for database 'pgs_core
', file 'tsfp' on file 1.
Processed 1 pages for database 'pgs_core
', file 'tsfp_log' on file 1.
BACKUP DATABASE successfully processed 561 pages in 0.284 seconds (16.182
MB/sec).
0
ITIExperts1Author Commented:
yes, the testdump.bak file is there as well as the testdump.txt file ahsay support said to try and create. i have submitted that file to their tech support along with my results. i should mention sql is running on vista home premium and these commands seemed to work when i ran the command prompt as the administrator.
0
computerstreberCommented:
Ah.... That may have been the issue. I think with Vista you have to run OSQL commands in a command prompt that is running as the administrator.
0
ITIExperts1Author Commented:
Thank you for your help. I do not know if you are familiar with Ahsay backup software, but the errors I am getting are here:
[2010/04/09 08:50:14] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '\.'.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '\.'.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '\.'.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ' '.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ' '.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ' '.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
[2010/04/09 08:50:24] [erro] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

I have sent this log file to Ahsay for their assistance. If you can possibly shed any light on it, I'm all ears, if not. we can close this and I will accept the solution.
0
computerstreberCommented:
I am not familiar with Ahsay. on. However, the errors appear to be due to syntax issues, namely incorrect use of ['] and ["]. Do you have control over the statements being prepared or does the application to all the statement preparation? To test, you might try setting the quoted identifier option. However, I would not due this in production, as it may have some adverse results.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITIExperts1Author Commented:
I have accepted the solution. I will wait for Ahsay to reply before I proceed any further. Thank you again for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.