Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to do the following. I have about 50 DBs and a network NAS that acts as backup machine. I have made an sql script(lets say backup.sql) with 50 lines each of these say "BACKUP DATABASE ABC TO DISK='Y:\ABC.bak'". Also, I have a .bat file with a single command "sqlcmd -i backup.sql -o logs.txt". When I execute it I take a message in the log that say:

HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

I have to mention that I want to make the backup from sql server 2008. Any ideas? Thanks in advance.

share|improve this question
add comment

2 Answers 2

Why are you using a batch file to backup the databases? Why not put the code in a SQL Agent job and have that kick off the backup?

share|improve this answer
    
Perhaps the OP is using the Express edition? Even so, I'd probably go about that a different way as well. –  Thomas Stringer Sep 12 '11 at 1:43
    
As Surfer513 say, I have SQL express version. :( –  py_script Sep 12 '11 at 16:19
    
That explains why no SQL Agent. Are you running this backup job on the SQL Server itself or another machine? –  mrdenny Sep 12 '11 at 19:31
    
On the SQL server itself. –  py_script Sep 13 '11 at 19:33
    
Is it a named instance like SQLEXPRESS or is it the default instance? –  mrdenny Sep 13 '11 at 19:38
show 2 more comments

By default, sqlcmd connects to local machine, using integrated authentication. You need to specify options for server and login to your sqlcmd call. So you'd need something like:

sqlcmd -i backup.sql -o logs.txt -S ComputerA

Or change the backup.sql script to use sqlcmd mode and use :connect command to change the server.

See details on MSDN:

share|improve this answer
    
I added the switch -S 127.0.0.1 but nothing different happened :( –  py_script Sep 13 '11 at 19:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.