sqlcmd: Running Scripts, Automating Tasks and Practical Tips for SQL Server’s Command-Line Tool

SSMS covers most day-to-day work, but when you need to run the same script on ten servers, schedule a maintenance task, automate a deployment step or get in while the server isn’t responding, the command line comes to the front. sqlcmd is Microsoft’s command-line tool for SQL Server: it runs T-SQL commands and script files, writes results to files, can be parameterized with variables and returns an exit code on errors.

This guide covers basic sqlcmd usage, the parameters that matter most for automation, scripting variables, the encryption default that comes with newer drivers, and what to watch for to use it securely.

The Two Versions of sqlcmd

There are two sqlcmd tools today:

• sqlcmd (ODBC): the classic version that comes with SQL Server setup and the Microsoft Command Line Utilities package. It uses the ODBC driver.
• sqlcmd (Go): a newer version that runs on Windows, Linux and macOS as a single executable. It supports most of the classic parameters, and adds extra commands such as creating containers for local development.

The output of sqlcmd -? tells you which one you’re using. The examples in this article work with both.

Basic Connection and Query

# Run one query with Windows authentication and exit
sqlcmd -S SERVER -E -Q "SELECT @@SERVERNAME, @@VERSION"

# Named instance and a specific database
sqlcmd -S SERVER\InstanceName -E -d SalesDB -Q "SELECT COUNT(*) FROM dbo.Orders"

# Interactive session (type commands and run them with GO)
sqlcmd -S SERVER -E

-Q runs the query and exits, while -q runs it and stays in the interactive session. -E uses Windows authentication (a trusted connection).

The Most Important Parameters for Automation

-b is the most overlooked and most important parameter in automation. Without it, sqlcmd can exit as if it succeeded even when the script failed, and the scheduled task or CI/CD pipeline never sees the error.

sqlcmd -S SERVER -E -b -i deploy.sql -o deploy_log.txt
if ($LASTEXITCODE -ne 0) { Write-Error "Deployment failed, log: deploy_log.txt"; exit 1 }

Scripting Variables

To run the same script in different environments, pass values in from outside. Inside the script, variables are used as $(Name):

-- take_backup.sql
BACKUP DATABASE [$(DbName)]
TO DISK = N'$(BackupPath)$(DbName)_copy.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;

sqlcmd -S SERVER -E -b -i take_backup.sql -v DbName="SalesDB" BackupPath="D:\Backup\"

sqlcmd Commands: :r, :setvar, :connect, :on error

Besides T-SQL, sqlcmd recognizes its own commands that start with a colon. They keep large deployment scripts organized:

:on error exit
:setvar Environment "TEST"

PRINT N'Environment: $(Environment)';

-- Include other files in order
:r .\01_tables.sql
:r .\02_views.sql
:r .\03_procedures.sql

-- Switch to another server within the same script
:connect REPORTSERVER
SELECT @@SERVERNAME;

You can use these commands in SSMS too: with Query › SQLCMD Mode on, the query window understands sqlcmd syntax. That lets you try a script in SSMS first and then run it unchanged from the command line.

Encryption and Certificate Errors

Like the newer ODBC drivers, current sqlcmd versions encrypt the connection by default and validate the server certificate. If the server uses its own self-signed certificate, the connection fails with a certificate error. The -C parameter connects by trusting the certificate, but because it skips validation it’s only a temporary workaround.

Our connection errors guide explains the background of certificate errors.

Practical Scenarios

• Multi-server checks: a PowerShell loop that reads a server list from a file and runs the same check script on each one.
• Deployment: scripts ordered with :r, and a safe release that stops at the first error with -b and :on error exit.
• Scheduled jobs: maintenance scripts with output written to a file, in Agent CmdExec steps or Windows Task Scheduler.
• In a crisis: connecting through the DAC with sqlcmd -S SERVER -E -A -d master while the server isn’t responding.
• Exports: quick CSV output with -W -s "," -h -1. For large data transfers, bcp is the better tool.