Sql-server – Database backup over sqlcmd call from batch file

sql serversqlcmdstored-procedures

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 Server\100\Tools\Binn\;c:\Program
Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft
SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft\Web Platform
Installer\;C:\Program Files\Microsoft SQL
Server\120\Tools\Binn\;C:\Program Files\Microsoft SQL Server\Client
SDK\ODBC\110\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL
Server\120\Tools\Binn\;C:\Program Files\Microsoft SQL
Server\120\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL
Server\120\Tools\Binn\ManagementStudio\;C:\Program Files
(x86)\Microsoft SQL Server\120\DTS\Binn\;C:\Program Files
(x86)\Windows Kits\8.1\Windows Performance Toolkit\;C:\Program
Files\Microsoft DNX\Dnvm\;C:\Pro-face\Pro-Server EX\

Best Answer

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 Server\100\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.

Related Question