Database backup over sqlcmd call from batch file

Question

I'm trying to do a database backup by sqlcmd called from batch file but I can't achieve that. I'm getting error "Sqlcmd: Error: Connection failure. SQL native Client is not installed correctly. To correct this, run SQL Server Setup". I was trying this : https://stackoverflow.com/questions/5083189/sqlcmd-wont-run-is-this-a-config-issue but every attempt of changing PATH environmental variable gives me another error :"command sqlcmd is not recognized as an internal or external command, operable program or batch file".

Can someone prompt me what I'm doing wrong ? Do I need to modify my PATH variable in some particular way ?

Batch file content :

sqlcmd.exe -S .\EXPRESS -E -Q "USE MyDatabase GO EXEC sp_BackupDB"

Stored proedure which makes backup - works fine from Management Studio

USE master
go
BEGIN TRY
 SET NOCOUNT ON;
 DECLARE
 @path VARCHAR(100)
 SELECT @path = 'c:\MyDatabase.bak'
 --SELECT @path
 -- BACKUP --
 BACKUP DATABASE MyDatabase TO DISK = @path WITH INIT;
END TRY
BEGIN CATCH
 PRINT 'error';
END CATCH
GO

PATH content :

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;c:\Program Files (x86)\Microsoft SQL Server100円\Tools\Binn\;c:\Program Files\Microsoft SQL Server100円\Tools\Binn\;c:\Program Files\Microsoft SQL Server100円\DTS\Binn\;C:\Program Files\Microsoft\Web Platform Installer\;C:\Program Files\Microsoft SQL Server120円\Tools\Binn\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC110円\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server120円\Tools\Binn\;C:\Program Files\Microsoft SQL Server120円\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server120円\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server120円\DTS\Binn\;C:\Program Files (x86)\Windows Kits8円.1\Windows Performance Toolkit\;C:\Program Files\Microsoft DNX\Dnvm\;C:\Pro-face\Pro-Server EX\

• spaghettidba: What if you specify the full path to SQLCMD in your batch file?
• Krismorte: try this sqlcmd.exe -S .\EXPRESS -E -Q "EXEC MyDatabase..sp_BackupDB"

Answer 1

Do this:

1.> Open a Command Prompt window and type: cd\ dir sqlcmd.exe /s

Our Command Prompt window returned the following: C:\Program Files\Microsoft SQL Server100円\Tools\Binn

Note: if no sqlcmd.exe is found something happened with your SQL install. To get you up and running I'd simply download the utilities themselves here:

https://www.microsoft.com/en-us/download/details.aspx?id=36433

2.> From the Command Prompt window command menu Mark and highlight that path that was returned and press Enter to put it in the paste buffer.

3.> Edit the System path (not the user path) within Windows and paste the path you just found just after all the %SYSTEMROOT% path references and click OK. You may need to restart your server for this to take affect.

Answer 2

You might have multiple sqlcmd installed in your client computer. To find out which sqlcmd is being used, type the following in a Windows Command Prompt:

where sqlcmd

This will list the various sqlcmd installed in your machine. You can reorder the preferences by modifying your PATH variable.