sqlcmd backup script failing

Question

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.

Databases are not configured to use trusted connections, applications are configured to connect to databases with trusted connections. — Justin Dearing, Mar 21 '12 at 19:08

score 1 · Accepted Answer · answered Mar 21 '12 at 19:07

1

Change remove one of the slashes in SET SERVER=.\\sqlexpress. It should be SET SERVER=.\sqlexpress

answered Mar 21 '12 at 19:07

Justin Dearing

1,017
10
33

Thanks, that and the closing double quotes around the query were missing. – Bryan Mar 22 '12 at 08:09

sqlcmd backup script failing

1 Answers1