Restoring a SQL Server Database Backup using Docker

I am currently updating several of my GitHub projects to use Docker, so it's easier for users to get started. The WideWorldImporters project requires a SQL Server 2022 database and we'll need to restore Microsofts WideWorldImporters OLTP Database to it:

This should have been a very straightforward process, but the sqlcmd coming with the SQL Server 2022 Docker image apparently hangs when restoring a database. So we also need to download the latest set of mssql-tools when building the image. Maybe this article saves a poor soul some minutes.

All code can be found in a Git repository at:

Creating the SQL Server 2022 Docker Image

We put the T-SQL Code to restore the database into a file restore.sql:

RESTORE DATABASE WideWorldImporters
    FROM DISK = "/tmp/backup/WideWorldImporters-Full.bak"
    WITH 
        MOVE "WWI_Primary" TO "/var/opt/mssql/data/WideWorldImporters.mdf",
        MOVE "WWI_Userdata" TO "/var/opt/mssql/data/WideWorldImporters_UserData.ndf",
        MOVE "WWI_Log" TO "/var/opt/mssql/data/WideWorldImporters.ldf", 
        MOVE "WWI_InMemory_Data_1" TO "/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1"
GO

After the database has been restored, we'll add a user wwi with the db_datareader and db_datawriter roles assigned. In a file users.sql we add:

USE [WideWorldImporters]
GO

CREATE LOGIN wwi WITH PASSWORD = N'340$Uuxwp7Mcxo7Khy'
GO

CREATE USER wwi FOR LOGIN wwi
GO

EXEC sp_addrolemember N'db_datareader', N'wwi'
GO

EXEC sp_addrolemember N'db_datawriter', N'wwi'
GO

These two files should be executed once the SQL Server is ready, so we put it into a shell script create-db.sh. Instead of complex logic, I just wait 30 seconds for the SQL Server to finish initialization:

#!/bin/bash

# Wait for SQL Server to complete
sleep 30s

# Run the setup script to create the DB and the schema in the DB
/opt/mssql-tools18/bin/sqlcmd -S localhost -C -U sa -P $MSSQL_SA_PASSWORD -d master -i restore.sql
/opt/mssql-tools18/bin/sqlcmd -S localhost -C -U sa -P $MSSQL_SA_PASSWORD -d master -i users.sql

In the entrypoint.sh, which is the Entrypoint for the Docker image, we then run the create-db.sh script in the background and spin up the SQL Server using the sqlservr executable:

#!/bin/bash

# Start the script to restore the DB and user
/tmp/backup/create-db.sh &

/opt/mssql/bin/sqlservr

Now we can write the Dockerfile, which installs the latest mssql-tools18 (including sqlcmd) and copies the scripts to the Docker image. Finally our entrypoint.sh is set as the Entrypoint, so it starts with the container.

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

WORKDIR /tmp/backup

# Install Packages, because built-in sqlcmd has a bug ...
USER root

RUN apt-get update \
    && ACCEPT_EULA=Y apt-get install mssql-tools18 unixodbc-dev -y \
    && wget -q  https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak

COPY restore.sql .
COPY users.sql .
COPY create-db.sh .
COPY entrypoint.sh .

ENTRYPOINT ["/bin/bash", "/tmp/backup/entrypoint.sh"]
version: '3.8'

networks:
  wwi:
    driver: bridge

services:
  db:
    container_name: wwi-sqlserver2022
    build:
      context: ./db/
      dockerfile: Dockerfile
    networks:
      - wwi
    ports:
      - "1533:1433"
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$$MSSQL_SA_PASSWORD" -C -Q "SELECT 1" || exit 1
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "StrongPassword@"

Conclusion

And that's it! Running a docker-compose up will now build a Docker image with an SQL Server 2022 and the WideWorldImporters Backup restored.


How to contribute

One of the easiest ways to contribute is to participate in discussions. You can also contribute by submitting pull requests.

General feedback and discussions?

Do you have questions or feedback on this article? Please create an issue on the Repositories issue tracker.

Something is wrong or missing?

There may be something wrong or missing in this article. If you want to help fixing it, then please make a Pull Request to this file.