sqlcmd backup script failing

Posted by Bryan on Server Fault See other posts from Server Fault or by Bryan
Published on 2012-03-21T16:36:08Z Indexed on 2012/03/21 17:31 UTC
Read the original article Hit count: 462

I'm trying to use a simple batch script to backup a local instance of SQL Express 2012, as follows:

@echo off
SET BACKUP_DIR=E:\BackupData
SET SERVER=.\\sqlexpress
set dom=%date:~0,2%
set month=%date:~3,2%
set year=%date:~6,4%
set file=%year%-%month%-%dom%
sqlcmd -S %SERVER% -d master -Q "exec sp_msforeachdb 'BACKUP DATABASE [?] TO DISK=''%BACKUP_DIR%\?.Full.%file%.bak'''

The script is failing to run with the following error:

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Client unable to establish connection due to prelogin failure.

This is on Server 2008 R2, my SQL database (on localhost) instance is named SQLEXPRESS. There is an instance of SQL Express 2008 on the system (hence client 10.0).

The database is configured to use a trusted connection, and the .net desktop software deployed on our network PCs is able to access the database without any problem.

Am I missing something obvious here, I've done a fair amount of searching for this error message, and haven't found anything that has been particularly useful so far.

© Server Fault or respective owner

Related posts about backup

Related posts about windows-server-2008-r2