Command line BACPACs to Docker SQL Server instances

A quick walkthrough of creating a Docker SQL instance, pushing data to it, taking a backup from the host command line (using BACPAC format) and pushing that data to a new instance. This made me feel a bit more confident working with SQL from the command line, and especially working with backups, which should make scripting these actions a bit easier.

A New SQL Instance

We'll use Docker to create a brand-new SQL instance.  Because Microsoft no longer releases SQL Server on Windows, we'll need to use Docker in Linux mode.  The following commands will launch a SQL Server instance running with the free "Developer Edition".

docker pull mcr.microsoft.com/mssql/server:2022-latest

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Password12345" `

   -p 1433:1433 --name sql1 --hostname sql1 `

   -d  mcr.microsoft.com/mssql/server:2022-latest

(If you have SQL Server running locally, you may need to change that to something like -p 14330:1433, and update the following steps to use your new port number.) 

Now you have SQL Server running.  (Note, if you stop and recreate the container, your data will be lost.  See Docker's volumes for how to avoid that. But that's out of scope for this post.)

Running T-SQL Commands

We can run T-SQL commands from within the container by doing this:

docker exec -it sql1 bash

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P Password12345

Try running this:

select name from sys.databases;

go

You should see something like this:

name

---------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

We've been following steps from this article: Docker: Install containers for SQL Server on Linux - SQL Server | Microsoft Learn.   Go to the section Insert Data for steps to create an Inventory table with two rows.  When you can run "Select * from inventory; GO" and see your data, come back here.

Creating the BACPAC

BACPAC files are Microsoft's version agnostic backup method. They are required for taking backups from SQL Azure and are also useful for sharing backups in a way that avoids issues with SQL Server visions, which come up with the standard backup (.bak) format.

First, we need SQLPACKAGE.exe.  Try that on your command line.  If not found, you can install with 

dotnet tool install -g microsoft.sqlpackage

When that's done, run SQLPACKAGE to confirm it runs. In my case, I got an error beacuse I didn't have the right .NET version loaded (6.0), so was prompted to fix that. Clicking the provided link and running the installer fixed the issue.

Once you get the very detailed parameter options in response to sqlpackage  we are ready to run our export.

This syntax worked nicely for me. You'll need to update passwords and ports, etc.

sqlpackage.exe /Action:export /SourceServerName:127.0.0.1,1433 `

/SourceDatabaseName:TestDB  /SourceUser:sa /SourcePassword:Password12345 ` /TargetFile:test1.bacpac /SourceTrustServerCertificate:true

Or if you prefer abbreviations:

sqlpackage.exe /action:export /ssn:127.0.0.1,1433 /sdn:TestDB /su:sa /sp:Password12345 ` /tf:test1.bacpac /sourcetrustservercertificate:true

 

If all goes well, you should see output like this:

With the backup file created, we can test restoring it to a new database, "TestDB2":

sqlpackage.exe /action:import /tsn:127.0.0.1,1433 /tdn:TestDB2 /tu:sa /tp:Password12345 ` /sf:test.bacpac /targettrustservercertificate:true

Note that the "s" (source) and "t" (target) values have been flipped.

You should see something like this:

And you can verify your data is there with this one-liner:

 docker exec sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P Password12345 `

-d TestDB2 -q "select * from Inventory"

Note: On my machine, SQLCMD was available on my host, so I could also run this from my host:

sqlcmd -S localhost -U SA -P Password12345 -d TestDB2 -Q "select * from Inventory"

But that may be available because I installed SQL Server on my host earlier, so in the walkthrough I stuck with running this command from within the container.  And if you changed your port number, you'll need to pass "-S localhost,14330" etc.   Note the comma, not a colon, before the port number, which is pretty standard with SQL Server connections. Also note that when passing the query, -q keeps a prompt open for further SQL commands, but -Q returns immediately. Run sqlcommand -? for all the options.

Further Reading

Here are the posts that helped me get this working, or give more information on topics discussed:

• Setting up MSSQL on Docker: Docker: Install containers for SQL Server on Linux - SQL Server | Microsoft Learn
• Working with SQLPACKAGE: SqlPackage.exe - Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques (sqlshack.com)
• Installing SQLPACKAGE: Download and install SqlPackage - SQL Server | Microsoft Learn
• Microsoft discontinuing Windows images for SQL Server: Update- Beta program for SQL Server on Windows container is suspended. - Microsoft Community Hub
• Installing a BACPAC using SQL-Server Management Studio (SSMS): Import a BACPAC file to create a new user database - SQL Server | Microsoft Learn