sqlcmd is the command-line utility for SQL Server — it connects to any SQL Server instance, executes T-SQL statements, and runs script files. It is essential for automation: CI/CD pipelines use sqlcmd to create databases, run migrations, and seed test data without a graphical interface. sqlcmd also runs inside Docker containers, enabling database setup scripts that work identically in development and production.
sqlcmd Fundamentals
-- ── Installing sqlcmd ──────────────────────────────────────────────────────
-- Windows: included with SQL Server or downloadable from Microsoft
-- macOS: brew install sqlcmd (or brew install microsoft/mssql-tools/mssql-tools)
-- Linux: apt-get install mssql-tools
-- Docker: included in mcr.microsoft.com/mssql/server image
-- ── Basic connection examples ──────────────────────────────────────────────
-- Connect to LocalDB (Windows only):
-- sqlcmd -S "(localdb)\MSSQLLocalDB" -E
-- Connect with SQL Server auth:
-- sqlcmd -S localhost -U sa -P "YourPassword123!" -d BlogApp
-- Connect to Azure SQL:
-- sqlcmd -S yourserver.database.windows.net -U admin -P "password" -d BlogApp
-- ── Run a query interactively ─────────────────────────────────────────────
-- After connecting, type SQL then GO to execute:
-- SELECT @@VERSION
-- GO
-- USE BlogApp
-- GO
-- SELECT TOP 5 Title FROM Posts ORDER BY CreatedAt DESC
-- GO
-- EXIT
-- ── Execute a script file ──────────────────────────────────────────────────
-- sqlcmd -S localhost -U sa -P "YourPassword!" -i CreateBlogAppDatabase.sql
-- ── Output to file ────────────────────────────────────────────────────────
-- sqlcmd -S localhost -U sa -P "YourPassword!" -Q "SELECT * FROM Posts" -o results.txt
-- ── Use variables in scripts ──────────────────────────────────────────────
-- sqlcmd -S localhost -v DbName="BlogApp_Test" -i create_db.sql
-- In the script: CREATE DATABASE $(DbName)
-- ── BlogApp database setup script (CreateBlogAppDatabase.sql) ────────────
-- :ON ERROR EXIT <-- stop on first error
-- USE master
-- GO
-- IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '$(DbName)')
-- BEGIN
-- CREATE DATABASE $(DbName)
-- PRINT 'Database $(DbName) created.'
-- END
-- ELSE
-- PRINT 'Database $(DbName) already exists.'
-- GO
-- sqlcmd -S localhost -U sa -P "Pass!" -v DbName="BlogApp" -i CreateBlogAppDatabase.sqlNote: The
GO command in sqlcmd (and SSMS) is not T-SQL — it is a batch separator recognised by the client tool. SQL Server receives and executes each batch (the statements between GO commands) separately. This matters for DDL statements: CREATE TABLE must be in its own batch (followed by GO) before the table can be referenced in subsequent statements. Forgetting GO after creating a database or table before trying to use it is a common scripting mistake.Tip: Use
:ON ERROR EXIT at the top of sqlcmd scripts to make the script stop on the first error and return a non-zero exit code. Without this, sqlcmd continues executing even after errors, which can cause partially-executed migration scripts that leave the database in an inconsistent state. In CI/CD pipelines, the non-zero exit code causes the pipeline step to fail visibly rather than silently continuing with a broken database.Warning: Never pass the SQL Server password directly on the command line in scripts committed to source control:
sqlcmd -P "MyPassword" visible in shell history and log files. Use environment variables: sqlcmd -P "$SQL_PASSWORD" where SQL_PASSWORD is set in the CI/CD environment’s secret variables. For interactive local development, Windows Authentication (-E flag) avoids passwords entirely. For Docker-based development, use Docker secrets or environment variables injected at container start.Common Mistakes
Mistake 1 — Missing GO batch separator between CREATE and USE (invalid object name)
❌ Wrong — CREATE DATABASE BlogApp followed immediately by USE BlogApp without GO; database does not exist yet when USE executes.
✅ Correct — always add GO after CREATE DATABASE and before USE to ensure the database is created before switching to it.
Mistake 2 — Hardcoding passwords in sqlcmd commands in scripts
❌ Wrong — sqlcmd -S server -U sa -P "hardcoded" committed to git; credentials exposed.
✅ Correct — use environment variables: sqlcmd -S $SQL_SERVER -U $SQL_USER -P $SQL_PASSWORD.