I have a homebrewed backup system for SQL Server 2008 Express on Windows Server 2008. A scheduled task runs a .bat file, which in turns calls sqlcmd.exe to run a .sql file that backs up all of the databases to .bak files. The scheduled task works fine if I right-click on it and click Run, but when the scheduled task runs in the middle of the night, sqlcmd.exe can't connect to the server, producing the error:
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Unable to complete login process due to delay in opening server connection.
I have the scheduled task set to run as myself, so the user is always the same. It's set to run whether I'm logged on or not, and set to store the password. It's also set to "run with highest privileges".
The sqlcmd.exe line of the batch script is:
"c:\program files\microsoft sql server\100\tools\binn\sqlcmd.exe" -s 127.0.0.1\SQLEXPRESS -i c:\web\backup_mssql.sql -o sqlcmd.log -b -E
I know that the docs say that the -s argument should be -S, but the command can't ever connect when I use -S.
The question is: why can this connect during the day but not when it runs automatically at night?