Use sqlcmd to create and query a SQL Server container for development

In the past, you may have downloaded and installed SQL Server Developer Edition on your computer to test features, write queries, and have a sandbox. Do you want a more lightweight development environment that's quicker to install and use? Enter Docker or Podman, sqlcmd, and SQL Server containers. 

 

Our modern sqlcmd gives you the capability to query your existing SQL Server and Azure SQL Database instances. It also introduces new features such as creating containers in one line of code, using an existing backup file to restore a database when the instance is created, and opening Azure Data Studio for data exploration in one command. Here is how you can do this end-to-end. 

 

 

The prerequisite to use sqlcmd for containers is having a container runtime installed such as Docker or Podman. 

 

Once that's done, the next step is to install sqlcmd from a package manager - no need to download a .exe or .zip any longer! On Window, this would be 

winget install sqlcmd

If you have the ODBC sqlcmd installed (which comes with SQL Server or may have been downloaded separately), you will need to close and reopen your shell for the Go sqlcmd to become the default.

 

 

You can also update your PATH variable to indicate which will take precedence. To do so in Windows 11, open System settings and go to About. Select Advanced system settings. When System Properties opens, select the Environment Variables button. In the lower half, under System variables, select Path and select the Edit button. Ensure that the location sqlcmd is saved to (C:\Program Files\SqlCmd is default) is listed before C:\Program Files\Microsoft SQL Server\<version>\Tools\Binn.

 

Finding what commands to use 

 

You will still find your favorite sqlcmd flags here, but to discover the new commands and flags, use 

sqlcmd --help 

 

 

To list the options for the create command, use  

sqlcmd create --help

At this time, SQL Server and Azure SQL Edge are available. 

 

To find the options available for SQL Server, use 

sqlcmd create mssql --help 

 

With a lot of options, here are a few flags of interest when you're getting started. 

--accept-eula	Accepts the SQL Server EULA. Either pass this in each time you create a container, or set the SQLCMDPASSWORD environment variable.
--tag	Which tag, or SQL Server container version, to use.
--port	Which port to use.
--using	Will download a .bak file into the container and restore it as a user database during the create process.

 

To view the list of tags, or versions, run 

sqlcmd create mssql get-tags

You can download and install any image you choose. The default will always be 'latest', which at this time is SQL Server 2022. However, previous versions are available if you need parity with an existing instance you have. 

 

Here is how to create a SQL Server 2019 container mapped to port 1435 using the WideWorldImporters database with one command. 

sqlcmd create mssql --accept-eula --port 1435 --tag 2019-latest --using https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak

 

 

To verify you have a container with SQL Server 2019, run 

sqlcmd query "SELECT @@VERSION"

 

To verify the database WideWorldImporters was restored, run 

sqlcmd query "SELECT name, database_id, state_desc, recovery_model_desc FROM sys.databases"

 

Note that you no longer have to make a connection to the server using sqlcmd syntax such as -S with -E or -U and -P. The modern sqlcmd has created a configuration file that contains your server name, address, port, and credentials and stores this as a context. (This may sound familiar if you've used Kubernetes!) When you create a container, sqlcmd automatically queries that context. You can even have multiple containers, each with their own context, and switch between them. 

 

When you want to do more data exploration or management, you can do so in Azure Data Studio. sqlcmd will open Azure Data Studio in the context of the server and the database, with your credentials. Use 

sqlcmd open ads 

 

If you are done with the container, you can delete it by running 

sqlcmd delete 

 

If a user database exists, you will need to drop the database or delete with the --force command. 

sqlcmd delete --force

Like what you see and want to try it out? 

winget install sqlcmd 
sqlcmd create mssql --accept-eula 

 

Have ideas for how to make it better? Give us feedback!