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.
Best Answer
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?