sqlcmd and bcp: SQL Server from the command line

sqlcmd runs T-SQL from a terminal and bcp moves table data in and out of files at speed. Between them they cover everything you would otherwise click through in SSMS and cannot automate: running migration scripts from CI, taking a backup from cron, dumping a query to CSV, loading a million rows from a file in seconds. The two commands you will use most are sqlcmd -S host,port -U user -d db -C -b -i script.sql and bcp dbo.Table out table.dat -n -S host,port -U user -d db. The rest of this guide is the flags around them, and the defaults that changed in version 18 and broke a great many scripts.

Which sqlcmd you have#

There are two programs called sqlcmd, and they mostly accept the same arguments:

• The ODBC sqlcmd - the original, shipped with SQL Server and in Microsoft's mssql-tools18 package for Linux and macOS (installed to /opt/mssql-tools18/bin/). It needs the ODBC Driver for SQL Server installed alongside.
• go-sqlcmd - a newer, single-binary rewrite in Go, installed with winget install sqlcmd on Windows or brew install sqlcmd on macOS. It needs no ODBC driver, adds a few conveniences such as creating a local SQL Server container, and aims to be a drop-in replacement for scripts.

For scripting, either works. Where behaviour differs between them, this guide says so. bcp exists only in the ODBC flavour, from the same mssql-tools18 package or a SQL Server install on Windows. Check what you have with:

$ sqlcmd -?$ bcp -v

The version 18 tools made one change that matters more than all the others: connections are encrypted by default, and the server's certificate is validated. A server with a self-signed certificate - which is most development instances and many hosted ones - then fails with a certificate chain error. Pass -C to sqlcmd (trust the server certificate) and -u to bcp 18, or install a certificate the client trusts. Scripts written for the old tools without either flag are the usual reason a CI job broke after a runner image update.

Connecting#

$ export SQLCMDPASSWORD='the-generated-password'$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C1> SELECT @@VERSION;2> GO

The flags:

Passing the password with -P puts it in your shell history and in the process list where any other user on the machine can read it. SQLCMDPASSWORD in the environment, set from a secret store in CI, avoids both. SQLCMDSERVER, SQLCMDUSER and SQLCMDDBNAME exist too, so a script can carry no connection details at all.

Without -d, you land in the login's default database. On RE:NODE, the database created for you is already sa's default, so sqlcmd -S host,port -U sa -C puts you straight into it.

Running queries and scripts#

In interactive mode, nothing runs until you type GO on its own line. GO is not T-SQL: it is a batch separator understood by sqlcmd and SSMS, which split the script at each GO and send the pieces one at a time. That is why CREATE PROCEDURE must be the first statement in its batch and why a variable declared before a GO is gone after it. GO 100 runs the preceding batch a hundred times, which is handy for generating test data.

For one-off commands, -Q runs a query and exits:

$ sqlcmd -S db.example.net,14330 -U sa -C -Q "SELECT name, state_desc FROM sys.databases"

-q (lower case) runs it and stays in interactive mode. For files, -i:

$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C -b -i 001_schema.sql -i 002_seed.sql

Several -i files run in order in one session. Inside a script, the commands that start with a colon are sqlcmd directives rather than T-SQL:

SSMS understands the same directives if you switch on SQLCMD Mode from the Query menu, which lets one script work both interactively and in a pipeline.

If your script contains non-ASCII text in N'...' literals, tell the ODBC sqlcmd the file's encoding with -f 65001 for UTF-8, or save the script as UTF-8 with a byte-order mark. Without either, accented characters can arrive mangled. SQL Server collations and Unicode explains why the N prefix matters in the first place.

Variables and exit codes for automation#

Scripting variables make one script work against several environments. Reference them as $(Name) and supply them with -v:

:on error exitUSE [$(DbName)];IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'$(ReaderUser)')    CREATE USER [$(ReaderUser)] FOR LOGIN [$(ReaderUser)];ALTER ROLE db_datareader ADD MEMBER [$(ReaderUser)];GO

$ sqlcmd -S db.example.net,14330 -U sa -C -b \    -v DbName="appdb" ReaderUser="reporting" \    -i create-reader.sql

Variables are substituted as text before the batch is sent, so they work anywhere - in object names, inside string literals, in USE. That is also their danger: never pass untrusted input through a scripting variable, because it is string concatenation with no escaping. Environment variables with the same name are picked up as scripting variables too, which is a tidy way to pass values from CI.

Exit codes are what make sqlcmd safe in a pipeline. By default, sqlcmd reports an error and carries on, and exits 0. Add -b and it exits with a non-zero code when an error of severity 11 or higher occurs, so the CI step fails instead of reporting success after a broken migration. Combine it with :on error exit in the script itself, and with SET XACT_ABORT ON inside any transaction, so that a failure halfway through neither continues nor leaves a transaction open.

$ sqlcmd -S "$DB_HOST,$DB_PORT" -U deployer -d appdb -C -b -i migrate.sql \    || { echo "migration failed"; exit 1; }

The same pattern makes sqlcmd the scheduler's arm on SQL Server Express, which has no SQL Server Agent. A nightly BACKUP DATABASE statement in a file, run by cron on another machine with -b so failures are visible, is a complete backup job; SQL Server backup and restore has the script and the parts that need care, such as where the .bak ends up. The same goes for statistics updates and index maintenance: anything you would have put in an Agent job step of type T-SQL can be a sqlcmd call on a timer.

For running migrations from an application framework instead, Entity Framework Core migrations in production shows how to produce an idempotent script that this same command can apply.

Exporting query results to a file#

sqlcmd can write result sets as delimited text, which is fine for quick exports and reports:

$ sqlcmd -S db.example.net,14330 -U sa -d appdb -C -W -h -1 -s "," \    -Q "SET NOCOUNT ON; SELECT OrderId, CustomerId, Total FROM dbo.Orders" \    -o orders.csv

Each of those flags is there for a reason, and leaving any one out produces a file that looks almost right:

• -W trims trailing spaces from columns, without which every value is padded to the column width.
• -h -1 removes the header row and the dashed line under it. Leave it out if you want headers - the dashed line is then the second row of your file.
• -s "," sets the column separator.
• SET NOCOUNT ON suppresses the (1234 rows affected) line at the end.

What sqlcmd does not do is quote values. A comma inside a name breaks the column alignment of that row. For data with free text, either choose a separator that cannot occur (a tab, or the pipe character), produce JSON from the query with FOR JSON PATH, or use bcp, which is also much faster at volume.

bcp: bulk export and import#

bcp reads and writes table data in bulk using the same fast path SQL Server uses for bulk loads. It has four modes, given as the second argument: out (a whole table), queryout (the result of a query), in (load a file into a table) and format (write a format file).

# Export a table in native format: fastest, exact, SQL Server only$ bcp dbo.Orders out orders.dat -S db.example.net,14330 -U sa -d appdb -n -u# Export a query as comma-separated text$ bcp "SELECT OrderId, CustomerId, Total FROM appdb.dbo.Orders WHERE Status = 1" \    queryout paid.csv -S db.example.net,14330 -U sa -c -t, -u# Load the native file into another server's empty table$ bcp dbo.Orders in orders.dat -S other.example.net,14330 -U sa -d appdb \    -n -E -b 10000 -h "TABLOCK" -u

bcp reads the password from a prompt if you leave out -P, which is the safer choice interactively. The format flags decide what the file contains:

And the flags that decide how a load behaves:

Code page handling for text files differs between Windows and the Linux and macOS builds of bcp, so if your data contains non-ASCII text in -c mode, check the -C option in the documentation for your platform, or use -w and save yourself the question.

When columns in the file do not match the table one for one, a format file maps them. Generate one from the table and edit it:

$ bcp dbo.Orders format nul -c -t, -f orders.fmt -S db.example.net,14330 -U sa -d appdb -u

Adding -x writes the XML variant, which is easier to read. Pass the file with -f orders.fmt on the in or out command.

Loading fast, and the alternatives#

A bulk load is fastest when it is minimally logged: the table is locked (-h "TABLOCK"), the database is in the simple or bulk-logged recovery model, and the target is a heap or an empty table. Under those conditions SQL Server logs page allocations rather than every row, which is several times faster and keeps the transaction log small. SQL Server recovery models and log growth explains why a large load in full recovery can grow the log by the size of the data.

For a large one-off load into a table with several nonclustered indexes, it is often quicker to drop or disable them, load, and rebuild than to maintain them row by row. Check constraints and foreign keys are not checked during a bcp load by default, and are then marked untrusted; add -h "CHECK_CONSTRAINTS" if you need them enforced, or re-validate afterwards with ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ALL.

The server-side alternatives are BULK INSERT and OPENROWSET(BULK ...), which read a file from the database server's own disk and, since SQL Server 2017, understand CSV properly with FORMAT = 'CSV' and FIELDQUOTE. They need the file on the server, and on Linux they need sysadmin, because the bulkadmin role is not supported there. For application code, every driver has a bulk-copy API - SqlBulkCopy in .NET, fast_executemany in pyodbc - that uses the same protocol as bcp without a temporary file. Moving a whole database rather than a few tables is better done with a backup or a BACPAC; migrating a database to SQL Server hosting compares the options.

Troubleshooting#

SSL Provider: certificate chain was issued by an authority that is not trusted. The version 18 encryption default. Add -C to sqlcmd or -u to bcp, or install a trusted certificate on the server.

Named Pipes Provider: Could not open a connection. The client never reached the server. Check the host, the port, the comma between them, and any firewall in the way.

Login failed for user. Wrong password, a login that does not exist, or a default database the login cannot open. Add -d master to test the login on its own.

Invalid object name in bcp. Qualify the table with the database (appdb.dbo.Orders) or pass -d. In queryout mode, always use three-part names.

Unexpected EOF encountered in BCP data-file. The terminators in the command do not match the file, often Windows \r\n line endings loaded with -r \n. Use -r 0x0a or convert the file.

String or binary data would be truncated. A value in the file is longer than the column. SQL Server 2019 and later name the column and value in the message; widen the column or clean the data.

FAQ#

Is sqlcmd available on Linux and macOS?

Yes. Microsoft publishes mssql-tools18, which contains sqlcmd and bcp, for the main Linux distributions and macOS, and go-sqlcmd installs through Homebrew. Both connect to any SQL Server on any platform.

How do I stop sqlcmd from carrying on after an error?

Use -b on the command line so it exits with a non-zero code, and put :on error exit at the top of the script so it stops at the first failing batch. Inside transactions, add SET XACT_ABORT ON so the transaction rolls back instead of staying open.

What is the fastest way to copy one table between two SQL Servers?

bcp out in native format (-n) from the source, then bcp in with -h "TABLOCK" and a batch size into an empty table on the target. Native format skips all text parsing, and the table lock allows minimal logging.

Can bcp export column headers?

Not directly. The usual workaround is a queryout with a UNION ALL of a header row cast to text, or exporting with sqlcmd instead, which writes headers by default. For data going to a spreadsheet, either is fine.

Why does my password with special characters fail in sqlcmd?

The shell is interpreting characters like $, ! or & before sqlcmd sees them. Put the password in single quotes when exporting SQLCMDPASSWORD, or read it from a file, rather than passing it with -P.