Quickstart: Create a new local copy of a database in a container with sqlcmd
In this quickstart, you'll use a single command in sqlcmd to create a new container, and restore a database to that container to create a new local copy of a database, for development or testing.
Prerequisites
- A container runtime installed, such as Docker or Podman
- Download and install Azure Data Studio
- Install the latest sqlcmd
Remarks
Installing sqlcmd (Go) via a package manager replaces sqlcmd (ODBC) with sqlcmd (Go) in your environment path. Any current command line sessions need to be closed and reopened for this change to take to effect. sqlcmd (ODBC) isn't removed, and can still be used by specifying the full path to the executable.
You can also update your PATH
variable to indicate which version takes precedence. To do so in Windows 11, open System settings and go to About > Advanced system settings. When System Properties opens, select the Environment Variables button. In the lower half, under System variables, select Path and then select Edit. If the location sqlcmd (Go) is saved to (C:\Program Files\sqlcmd
is default) is listed before C:\Program Files\Microsoft SQL Server\<version>\Tools\Binn
, then sqlcmd (Go) is used.
You can reverse the order to make sqlcmd (ODBC) the default again.
Download and install sqlcmd (Go)
sqlcmd (Go) can be installed cross-platform, on Microsoft Windows, macOS, and Linux.
winget (Windows Package Manager CLI)
Install the Windows Package Manager Client if you don't already have it.
Run the following command to install sqlcmd (Go).
winget install sqlcmd
Chocolatey
Install Chocolatey if you don't already have it.
Run the following command to install sqlcmd (Go).
choco install sqlcmd
Direct download
Download the corresponding
-windows-x64.zip
or-windows-arm.zip
asset from the latest release of sqlcmd (Go) from the GitHub code repository.Extract the
sqlcmd.exe
file from the downloaded zip folder.
What problem will we solve?
This quickstart walks through the process of creating a local copy of a database, then querying it to analyze spending by customer.
Create a new container and restore a database
Create a new SQL Server instance in a container using the latest version of SQL Server. The command also restores the WideWorldImporters
database.
Open a new terminal window and run the following command:
sqlcmd create mssql --name sql1 --accept-eula --using https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak
Query the database in Azure Data Studio
Open Azure Data Studio and have a look at the data.
In the same terminal window, run the following command:
sqlcmd open ads
Now that you have a local copy of your database, you can run queries. Here is a query you can use to analyze spending by customer:
SELECT bg.BuyingGroupName AS CustomerName ,COUNT(DISTINCT i.InvoiceID) AS InvoiceCount ,COUNT(il.InvoiceLineID) AS InvoiceLineCount ,SUM(il.LineProfit) AS Profit ,SUM(il.ExtendedPrice) AS ExtendedPrice FROM Sales.Invoices i INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID INNER JOIN Sales.BuyingGroups bg ON c.BuyingGroupID = bg.BuyingGroupID GROUP BY bg.BuyingGroupName UNION SELECT c.CustomerName ,COUNT(DISTINCT i.InvoiceID) AS InvoiceCount ,COUNT(il.InvoiceLineID) AS InvoiceLineCount ,SUM(il.LineProfit) AS Profit ,SUM(il.ExtendedPrice) AS ExtendedPrice FROM Sales.Invoices i INNER JOIN Sales.Customers c ON i.CustomerID = c.CustomerID INNER JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID LEFT JOIN Sales.BuyingGroups bg ON c.BuyingGroupID = bg.BuyingGroupID WHERE bg.BuyingGroupID IS NULL GROUP BY c.CustomerName ORDER BY Profit DESC
How did we solve the problem?
You were able to quickly create a local copy of a database for development and testing purposes. With a single command, you created a new local instance and restored the most recent backup to it. You then ran another command to connect to it via Azure Data Studio. You then queried the database using Azure Data Studio to analyze spending by customer.
Clean up resources
When you're done trying out the database, delete the container with the following command:
sqlcmd delete --force
The --force
flag is used here for convenience since we are in a demo environment. In most cases, it's better to leave the --force
flag off to make sure you aren't inadvertently deleting a database you don't mean to.
Related content
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for