1

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.

Improve this question

2 Answers 2

Reset to default
1

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?

Improve this answer
7
  • 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, 2011 at 1:43
  • As Surfer513 say, I have SQL express version. :(
    – py_script
    Sep 12, 2011 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, 2011 at 19:31
  • On the SQL server itself.
    – py_script
    Sep 13, 2011 at 19:33
  • Is it a named instance like SQLEXPRESS or is it the default instance?
    – mrdenny
    Sep 13, 2011 at 19:38
0

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:

Improve this answer
1
  • I added the switch -S 127.0.0.1 but nothing different happened :(
    – py_script
    Sep 13, 2011 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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