Using sqlcmd CLI for SQL Server

Goal

Execute SQL Server queries and scripts from the command line using sqlcmd.

Installation

The sqlcmd.exe utility comes with Visual Studio 2019 or SQL Server 2019.

Location:

C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150

Add this path to your system PATH environment variable.

Execute a SQL Script

sqlcmd -S localhost -i C:\setupscripts\createUser.sql -o C:\setupscripts\createUser.log

Interactive Query Session

sqlcmd -S localhost -U sa -P password -d database

select * from transactions
go

Run from Docker Container

Connect to SQL Server running on host machine:

docker run --rm -it mcr.microsoft.com/mssql-tools
sqlcmd -S host.docker.internal -U [USER] -P [PASSWORD] -d [DATABASE]

select * from transactions
go

The host.docker.internal hostname resolves to the host’s localhost from within the container.

Run from Kubernetes Pod

kubectl run disposable-sql --image=mcr.microsoft.com/mssql-tools -it --rm -- bash
sqlcmd -S [IP],[PORT] -U [USER] -P [PASSWORD] -d [DATABASE]

select * from transactions
go