Charles Whitehead's Blog

Mocking a SQL Server Database (with Data!) using Docker and PowerShell

Mocking a SQL Server Database (with Data!) using Docker and PowerShell

Charles Whitehead's photo
Charles Whitehead
·

4 min read

When it comes to developing & testing applications that rely on SQL Server databases, traditional methods often involve setting up complex environments and dealing with the challenges of generating robust and relevant data.

By leveraging containerization though, developers can much more easily create lightweight, isolated environments that mimic SQL Server databases and with specific sets of data. By issuing just a few simple commands, developers can spin up containers with pre-defined schema and data, tailored specifically to their testing requirements.

In this article, we delve into the realm of mocking data by creating a SQL Server Docker container and populating it with a database that's restored from a .bak backup.

Note: This guide does not cover Docker Desktop installation... it assumes you already have it up and running correctly!

Obtaining the SQL Server Image

Open a regular PowerShell window and run the following:

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

This will automatically download the 2022-latest tag (version) of Microsoft's mcr.microsoft.com/mssql/server Docker image and make it available in your Docker Desktop Images. (Microsoft's Docker Hub)

With the SQL Server image now in hand, we can work towards creating a container.

Launching the Container Instance

Run the following from a PowerShell window to create a container named Hashnode that runs on port 1433:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=StrongPassword#1234" `
   -p 1433:1433 --name HashnodeDemo --hostname HashnodeDemo `
   -d `
   mcr.microsoft.com/mssql/server:2022-latest

Note: two environment variables MUST be passed in when first launching the container for the SQL Server to successfully start:

  1. Accepting the EULA with "Y"

  2. Setting a password for the SQL Server Super Admin (SA) user

When creating your own password, it must adhere to the SQL Server password policy.

Once the container launches, it'll be listed in the Docker Desktop Containers section (and hopefully as Running).

Using the credentials you created earlier, you should now be able to connect to the containerized SQL Server using a program like SSMS.

Restoring a .bak Backup to the SQL Server Container Database

We'll be using the lightweight AdventureWorksLT2022.bak sample Db backup from Microsoft Learn for this section.

High-level process:

  1. Copy the .bak into the Docker container.

  2. Validate the contents of the .bak from within the container.

  3. Restore the Database.

1. Copy the backup to the container

Open a PowerShell window and navigate to the directory where the .bak is on your machine. From there, run these two commands:

docker exec -it HashnodeDemo mkdir /var/opt/mssql/backup
docker cp AdventureWorksLT2022.bak HashnodeDemo:/var/opt/mssql/backup

This first creates a /backup directory within the Docker container, then copies the .bak from your machine to the container.

2. Validate the contents

Ultimately, the purpose of validation is to determine the names of the files to restore. If you already know all the file names and their extension types, you can skip this part.

Validating the copied-over .bak can be done one of two ways -- from the CLI or from SSMS.

To go the CLI route, use the following command:

docker exec -it HashnodeDemo /opt/mssql-tools/bin/sqlcmd `
  -S localhost -U SA -P "StrongPassword#1234" `
  -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/AdventureWorksLT2022.bak'"

Without special formatting though there's a strong chance you'll get a poorly formatted table as a response, something similar to the following:

That said. if you just run the SQL command from SSMS you'll get a much more friendly output.

RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/AdventureWorksLT2022.bak'

From this result, what we mainly care about is the LogicalName and the Type for each file.

3. Restore the database

Using details from the previous output, run the following statement to restore the database files.

docker exec -it HashnodeDemo /opt/mssql-tools/bin/sqlcmd `
   -S localhost -U SA -P "StrongPassword#1234" `
   -Q "RESTORE DATABASE AdventureWorksLT2012 FROM DISK = '/var/opt/mssql/backup/AdventureWorksLT2022.bak' WITH MOVE 'AdventureWorksLT2022_Data' TO '/var/opt/mssql/data/AdventureWorksLT2012.mdf', MOVE 'AdventureWorksLT2022_Log' TO '/var/opt/mssql/data/AdventureWorksLT2012_Log.ldf'"

Note that for each file you wish to restore, the general approach is

MOVE 'FileLogicalName' TO '/var/opt/mssql/data/FileLogicalName.FileType'

Ran from a PowerShell CLI it'll look like

and from SSMS

Once restored and after refreshing the Object Explorer view, you should see the restored database listed and can now make queries against the data!

Congratulations! You've successfully implemented a Docker container for SQL Server and restored a Db to it!

Takeaways

  • Docker can be used for containerizing SQL Servers

  • Databases can be restored to the container with just a few commands

  • Most (if not all) of this process can be automated

  • It's completely free and can easily be adopted by others

Thanks for reading and until next time, happy scripting!

References

DockerSQL ServerDatabasesDevops