Joey on SQL Server

A Love Letter to the Command Line Tool sqlcmd

Often overlooked, it's a powerful tool that can make your life a lot easier. Especially now that Microsoft has updated its functionality.

• By Joey D'Antoni
• 03/17/2023

It is safe to say that the command line tool sqlcmd has not been the sexiest component in the Microsoft Data Platform tools stack. In my unscientific poll of the SQL Server community on Twitter, only 46% of the respondents said they used sqlcmd in the last six months.  

I am probably a bit of an outlier, but ever since SQL Server landed on Linux (and more importantly, Docker containers), I regularly use this command line interface (CLI) to SQL Server and Azure SQL due to its lightweight nature. For both quick administrative work and automation, I like sqlcmd. Just six months ago, I wrote a process that generated a restore script to restore a client's QA database to their development environment that heavily depended on using sqlcmd.

There are many PowerShell options for interacting with SQL Server and Azure SQL, and while I use dbatoolsa lot, sqlcmd is just so light and efficient -- and the utility gets installed when SQL Server does, so it's always on a server.

I learned to love sqlcmd when I was working at Comcast, and we built a private cloud. I automated the install and post-install configuration of SQL Servers. If you have never had the fortune of installing SQL Server, you should know that the installation process offers a ton of options that can be confusing even to experienced administrators. When you combine that with the state of PowerShell as it related to SQL Server in 2012, I relied heavily on old-school batch scripts and sqlcmd a lot (you can do everything we did and more with dbatools now). A fundamental example of how you could use sqlcmdis shown below:

You would save the following script to a file called GenBackup.sql.

SELECT  'BACKUP DATABASE ' + name + ' FROM sys.databases ' + ' TO DISK = /Users/Joey/'  + name + '.bak'

  FROM  sys.databases

You would then use sqlcmd to generate a backup script and then execute that backup script as follows:

sqlcmd -E -S localhost -i GenBackup.sql -o RunBackup.sql

  sqlcmd -E -S localhost -i RunBackup.sql -o Backup.log

I have simplified this example for brevity, but it's a straightforward example of how sqlcmd could be used to automate simple code. While some of the PowerShell options have since surpassed some of the functionality, Microsoft recently decided to invest in sqlcmd. I spoke with the development team in December to see a demo and provide some input. Last week they made the GitHub repository public, and I wanted to share some of my experiences using the new go-sqlcmd,as it is known.

Go-Sqlcmd started a couple of years ago as a new version of sqlcmd written in Go -- making it more easily portable across platforms. I first learned of it when I got a Macbook Pro with an Apple M1 processor. I could launch a SQL Server on Edge container (now you can use SQL Server), but I could not connect to it. When I talked to some people on the tools team at Microsoft, they mentioned this open source project, which was, at the time, just a cross-platform version of the existing tool.

However, with the newly released version of go-sqlcmd, which can still connect to an existing database to run queries, Microsoft has introduced powerful new commands to create and manage SQL Server containers and open Azure Data Studio to perform further query exploration and data management. This tool, built upon themssqldbdriver, is open source, cross-platform and cross-CPU architecture. Go-sqlcmd has features more in line with modern command-line features and flags (like -h or - - help for help).

You may ask why I am writing a full column about command-line tool updates, which is a valid question. I've been using this for a couple of weeks, and wanted to show you some of the functionality. But this is a boon for SQL developers. To take full advantage of go-sqlcmd you must have a container runtime installed on your machine, like Docker or Podman.

What you saw in that gif was me creating a new SQL Server 2022 container with the AdventureWorksLT database restored to it. I then run a few queries to show the instance's version and the databases are in the instance. While I did trim some time when I was creating this GIF, the process of creating the container and running the query was well under 20 seconds.

In this second GIF, I create an additional container with the same database running SQL Server 2019. This kind of rapid deployment (this supports all of the available images that date back to SQL Server 2017) can facilitate a number of testing scenarios for developers. The CLI supports several different flags to configure your database or container.

Go-sqlcmd is still very much a work in progress and is preview software. A couple of issues I've raised include the ability to specify the edition of SQL Server you would like (this is a supported scenario in Docker, which allows you to test Standard Edition easily). Currently, all containers use Developer Edition. Another piece of feedback we shared is the ability to restore from a non-public Web site, which might allow the ability to restore from an Azure Blob Storage account or an S3 bucket.

Having been a lifelong scripter and automator of things, I really like these new features in go-sqlcmd. They help bring more speed and simplicity to development teams, making it easier to start building applications with a SQL Server database. Or, if you are a more mature organization, it can help you automate unit testing against a wide range of SQL Server versions.