🎒 Resources

• sqlcmd on Microsoft Learn
• go-sqlcmd GitHub repository
• Azure Data Studio

📖 Introduction

I bet anyone who has worked with Microsoft SQL Server databases already knows sqlcmd. In this post, I’ll discuss the new Go-based version of this tool. Specifically, I’ll explain how you can leverage it to streamline your development workflow when interacting with Microsoft SQL Server databases.

✔️ Prerequisites

Although having Docker is not strictly required for working with SQL Servers, the scenarios I’ll be discussing rely on Docker. So please ensure that you have Docker installed on your machine.

Now let’s install the new sqlcmd tool. Depending on your platform, you have two options: use a package manager or download the binary directly from the GitHub repository’s releases page. Personally, I recommend the first option. Since I’m on macOS, I’ll use Homebrew:

1

brew install sqlcmd

Verify your installation:

1

sqlcmd --version

You should see something like this below:

1
2
3

sqlcmd: Install/Create/Query SQL Server, Azure SQL, and Tools

Version: 1.7.0

Anything above and equal to 1.0.0 should be fine. Let’s move on!

📁 Use Cases & Scenarios

I will focus on the developer’s point of view: whether you’re scaffolding a new project or looking to make your CI/CD pipeline more robust, the new sqlcmd can be invaluable. I will demonstrate it in two distinct scenarios - both of which are fairly common in my daily work.

Scenario 1: Spin Up a SQL Server Instance

Using sqlcmd, you can quickly create a SQL Server instance in a Docker container. While I typically prefer a Devcontainer with a pre-configured docker-compose.yml file, there are scenarios where this approach does not fit — such as reproducing a bug on a legacy system or validating changes across different SQL Server versions.

The following command will start the latest Developer Edition version of SQL Server listening on port 1433 - or the next one available on your system:

1

sqlcmd start mssql --name mssql1 --accept-eula

Depending on your connection’s speed, it might take a few minutes to pull the requested Docker image. Once it is done, you will see something like this:

1
2
3
4
5

Downloading mcr.microsoft.com/mssql/server:latest
Starting mcr.microsoft.com/mssql/server:latest
Created context "mssql1" in "/Users/tamas/.sqlcmd/sqlconfig", configuring user account...
Disabled "sa" account (and rotated "sa" password). Creating user "tamas"
Now ready for client connections on port 1433

Nice! But how can you connect to this instance? 🤔

1

sqlcmd config connection-strings

You will see something like this:

1
2
3
4
5

SQLCMD:  export 'SQLCMDPASSWORD=7dh**j6*XXXXXX4CHctAp396d%Xk*gZK&3C973E&I&Xs#o%78%'; sqlcmd -S 127.0.0.1,1433 -U tamas -d master
ADO.NET: Server=tcp:127.0.0.1,1433;Initial Catalog=master;Persist Security Info=False;User ID=tamas;Password=7dh**j6*XXXXXX4CHctAp396d%Xk*gZK&3C973E&I&Xs#o%78%;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;
JDBC:    jdbc:sqlserver://127.0.0.1:1433;database=master;user=tamas;password=7dh**j6*XXXXXX4CHctAp396d%Xk*gZK&3C973E&I&Xs#o%78%;encrypt=true;trustServerCertificate=true;loginTimeout=30;
ODBC:    Driver={ODBC Driver 18 for SQL Server};Server=tcp:127.0.0.1,1433;Database=master;Uid=tamas;Pwd=7dh**j6*XXXXXX4CHctAp396d%Xk*gZK&3C973E&I&Xs#o%78%;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;
GO:      sqlserver://tamas:7dh**j6*XXXXXX4CHctAp396d%Xk*gZK&3C973E&I&Xs#o%78%@127.0.0.1,1433?database=master;encrypt=true;trustServerCertificate=true;dial+timeout=30

So many choices! Whatever platform or programming language you use, probably there is an option to connect to this SQL Server instance.

I’ll use Azure Data Studio connecting to the SQL Server instance - feel free to use your preferred client. The steps are straightforward:

1. Copy the ADO.NET connection string.
2. In Azure Data Studio, click on the New Connection button.
3. Change the Input type to Connection String.
4. Paste the connection string into the Connection String field.
5. Click Connect.

And you’re in!

A Few Words About Contexts

We’ve set up one SQL Server instance using sqlcmd, but you’re not limited to just one instance. You can create multiple instances, even with different SQL Server versions. For example, let’s set up an SQL Server 2019 instance:

1

sqlcmd create mssql --name mssql2 --tag 2019-latest --accept-eula

After a few minutes, your SQL Server 2019 instance should be running. Connect to it using the same method (sqlcmd config connection-strings). If you’ve lost the connection string for the other instance - don’t worry: we can retrieve it.

This is where the concept of contexts comes into play. Every instance and its configuration belong to a context. The sqlcmd stores related information in the .sqlcmd/sqlconfig file located in your home directory.

Fetch the Configuration File

While you can view the file’s content using the cat command, it’s better to use config view:

1

sqlcmd config view

The result should be something like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

version: v1
endpoints:
- asset:
    container:
      id: 64d84e002087bb1f9fa8d1b994ac053b2e29f16ca6f7806e8b1411fde1226bb9
      image: mcr.microsoft.com/mssql/server:2022-latest
  endpoint:
    address: 127.0.0.1
    port: 1433
  name: mssql1
- asset:
    container:
      id: 30a9dd17d40cab63927a306c1da65f703e7a19f305f94c5131be600918141c4c
      image: mcr.microsoft.com/mssql/server:2019-latest
  endpoint:
    address: 127.0.0.1
    port: 1434
  name: mssql2
contexts:
- context:
    endpoint: mssql1
    user: tamas@mssql
  name: mssql
- context:
    endpoint: mssql2
    user: tamas@mssql2
  name: mssql2
currentcontext: mssql2
users:
- name: tamas@mssql
  authentication-type: basic
  basic-auth:
    username: tamas
    password-encryption: none
    password: REDACTED
- name: tamas@mssql2
  authentication-type: basic
  basic-auth:
    username: tamas
    password-encryption: none
    password: REDACTED

This YAML file becomes handy when we need to work with your contexts and instances during automation or CI/CD pipelines.

Currently, I have two contexts: mssql1 and mssql2. According to the currentcontext field, the mssql2 context - the later one I’ve created - is the active. To switch to the mssql1 context, the config use-context command will do the trick:

Switching Between Contexts

1

sqlcmd config use-context mssql1

Now every command I issue via the sqlcmd tool will interact with the mssql1 instance. Did I mention that you can execute SQL scripts directly from the command line on the active context’s database?

1
2
3
4
5
6
7
8
9

sqlcmd query "SELECT name FROM sys.databases"
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb

(4 rows affected)

That’s it! No need to fuss with connection strings and database clients to execute a simple query.

Prepare Some Test Data

Before we proceed to the second scenario, let’s do a quick exercise: scaffold a small database with some sample data. Since we have an open connection in Azure Data Studio, stay there and execute the following SQL scripts in the comfort of the graphical user interface.

Open a new query window (CMD / CTRL + N), paste the following SQL scripts, and execute them:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Create the database
CREATE DATABASE PrecipitationDB;
GO

USE PrecipitationDB;
GO

-- Create the Locations table
CREATE TABLE Locations (
    LocationID INT PRIMARY KEY,
    LocationName NVARCHAR(100),
    Latitude DECIMAL(9, 6),
    Longitude DECIMAL(9, 6)
);
GO

-- Create the PrecipitationRecords table
CREATE TABLE PrecipitationRecords (
    RecordID INT PRIMARY KEY IDENTITY(1,1),
    LocationID INT,
    RecordDate DATE,
    PrecipitationAmount DECIMAL(5,2),
    FOREIGN KEY (LocationID) REFERENCES Locations(LocationID)
);
GO

After executing the queries, insert some sample data into the tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

USE PrecipitationDB;
GO

INSERT INTO Locations (LocationID, LocationName, Latitude, Longitude)
VALUES
    (1, 'Budapest', 47.4979, 19.0402),
    (2, 'New York', 40.7128, -74.0060),
    (3, 'Sydney', -33.8688, 151.2093);

INSERT INTO PrecipitationRecords (LocationID, RecordDate, PrecipitationAmount)
VALUES
    (1, '2024-07-01', 0.5),
    (1, '2024-07-02', 0.2),
    (1, '2024-07-03', 0.0),
    (2, '2024-07-01', 1.0),
    (2, '2024-07-02', 1.3),
    (2, '2024-07-03', 0.7),
    (3, '2024-07-01', 0.4),
    (3, '2024-07-02', 0.0),
    (3, '2024-07-03', 0.0);

To be honest, I don’t know why I chose precipitation data for this example, as it has no relevance to the topic. Feel free to replace it with something more meaningful.

Backup Test Database

Paste the following into a new query window and execute it:

1
2

BACKUP DATABASE [PrecipitationDB] TO DISK = '/var/opt/mssql/data/PrecipitationDb.bak'
GO

This command will create a backup of the PrecipitationDB database in the /var/opt/mssql/data directory. We have only one thing left to do: fetch the .bak file from the Docker container to our local machine.

First, verify the container name of the SQL Server instance with the docker ps command. The NAMES column will show the container names. Now copy the .bak file from the mssql1 container to your local machine:

1

docker cp mssql1:/var/opt/mssql/data/PrecipitationDb.bak .

This will fetch the PrecipitationDb.bak file from the mssql1 container’s volume to your machine’s current directory:

1

Successfully copied 4.38MB to /Users/tamas/Desktop

Fantastic! 🎉 We have a backup of the PrecipitationDB sample database. But why did we do all this?

Scenario 2: Spin Up a SQL Server Instance With a Pre-Existing Database

There are several reasons why a developer might need to work from database backups. For instance, you might need to investigate a failing automated test, and the QA engineer has provided a backup of the database used during the test. Alternatively, you might need to switch between different versions of your product’s code base and use a database snapshot for each version to address issues caused by schema differences.

So let’s create a new SQL Server instance, but this time we will include the PrecipitationDB database - using the PrecipitationDb.bak file we fetched earlier.

1

sqlcmd create mssql --name mssql3 --accept-eula --using <<URL-of-PrecipitationDb.bak>>

Now here is the catch: currently, the sqlcmd tool does not support anything than HTTP or HTTPS URLs - in other words, you cannot use a local file path to provide the .bak file to the tool. So, you need to host the .bak file somewhere and provide the URL with the --using flag.

Most of the cases this might not be an issue, probably these backups are already stored in a cloud storage service like Azure Blob Storage or AWS S3. Or the CI/CD pipeline has a durable storage where failed jobs’ artifacts are stored.

Until the sqlcmd tool supports local file paths, I have a relatively simple workaround: running a local HTTP server to serve my .bak files - using the Nginx Docker image.

Serve Your Backup Files Locally

⏩ If you have a decent web server where you can store your .bak files, skip to the next section.

Here is the Dockerfile:

1
2

FROM nginx:alpine
VOLUME /usr/share/nginx/html

Build the image:

1

docker build -t sqlcmd-nginx .

Run the container when it is needed:

1

docker run -d -p 8080:80 --name sqlcmd-nginx -v /path/to/your/bak/files:/usr/share/nginx/html sqlcmd-nginx

Feel free to integrate this into your projects’ docker-compose.yml file, so you will have a local HTTP server bound to your workflow up and running alongside other services.

⚠️ Important: You need to get the local IP address of your machine to access the HTTP server from the Docker container created by the sqlcmd tool on the fly.

I’m on macOS, so I can get my IP address with the following command:

1

ipconfig getifaddr en0

🤔 Why is it so complicated?

As we are mimicking a real-world scenario, it naturally requires some extra effort. In a production environment, you would have a proper storage service to host these files.

Do you have a simpler solution? Share it with us in the comments below! 🙏

So, my IP address is 192.168.0.109 - I will refer to it in the next command, but please don’t forget to replace it with your own IP address.

Download and Attach the Database

Now - as we have the proper URL at hand - let’s create the mssql3 instance:

1

sqlcmd create mssql --name mssql3 --accept-eula --using http://192.168.0.109:8080/PrecipitationDb.bak

Get the connection string for the mssql3 instance - like we did earlier:

1

sqlcmd config cs

☝️ cs is a shorthand for connection-strings

Add the connection string to Azure Data Studio and connect to the mssql3 instance. Note that the connection string will contain the InitialCatalog attribute.

Now you should find the PrecipitationDB database with the Locations and PrecipitationRecords tables and the sample data we’ve created earlier on the mssql1 instance:

Cleaning Up

Once you have finished with the mssql3 instance, you can delete it using the following command:

1

sqlcmd delete

This will remove the mssql3 instance and its related context, reverting to the previous context—in this case, the `mssql2’ context.

If you prefer not to delete the instance but want to stop it temporarily, you can use the stop command:

1

sqlcmd stop

Please note, stopping an instance does not switch the context. You need to change the context manually to stop another one in a different contexts. I hope you already know how to start the instance again, but just in case:

1

sqlcmd start

Fantastic! Here is what we’ve done so far:

1. Created a SQL Server instance (mssql1) with minimal effort.
2. Set up a second SQL Server instance (mssql2) using a different version (2019 instead of 2022), without worrying about port conflicts or other issues.
3. Discussed the concept of contexts and how to switch between them.
4. Created a sample database (PrecipitationDb), populated it with data, and backed it up.
5. Set up a third SQL Server instance pre-populated with the PrecipitationDB database.

🤔 Conclusion

As we approach the end of this post, you might be wondering: is all the hassle with the sqlcmd tool worth it? Probably no. The features I have shown are very helpful in some specific cases - especially when working with CI/CD pipelines and automated tests.

I have only scratched the surface. It’s up to you how you can leverage the sqlcmd tool in your daily work. Here are some ways I use it:

1. Create a developer tenant of your product: spin up a SQL Server instance with a pre-populated database, extract the connection string using a script, and inject it into the configuration file of your application. Teardown is as simple as running sqlcmd delete. Wow! 🤯

2. Incorporate a database backup into your automated tests: on a failed case, create a .bak file from the database automatically. Developers could use this backup to investigate the issue with the --using flag of sqlcmd.

3. Embrace the power of contexts: switch between different SQL Server instances during development. Skip the hassle of running migrations back and forth, just switch the context and you are good to go.

I hope you enjoyed this post and found it useful. I wonder if you find my ideas helpful - do you have a better way to deal with such scenarios? Then share it with us in the comments below!

Until next time! 👋