Dockerizing SQL Server with Pre-Restored Databases

Docker Containers

Docker is a tool for creating lightweight, portable

To do this, right click on the database, select Tasks > Back Up, chose a file location for the backup, and select the ‘Full’ backup type. This process generates a .bak file, which can be used to restore the database in a Docker container.

Note: multiple databases can be preloaded onto a Docker image, but for this example we will just preload this DemoDb database.

Write a Dockerfile

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



# Accept license agreement

ENV ACCEPT_EULA=Y



USER root



# Copy files to container.

COPY DemoDb.bak /var/opt/mssql/DemoDb.bak

COPY pre-restore-db.sh /usr/src/app/pre-restore-db.sh



# Add executable permissions to script.

# Run database restore script with the password accessed as a secret.

# Remove backup files.

RUN --mount=type=secret,id=SA_PASSWORD \

  chmod +x /usr/src/app/pre-restore-db.sh \

  && SA_PASSWORD=$(cat /run/secrets/SA_PASSWORD) /bin/bash /usr/src/app/pre-restore-db.sh \

  && rm /usr/src/app/pre-restore-db.sh /var/opt/mssql/DemoDb.bak



# Ensure SQL Server is started when the container runs.

ENTRYPOINT ["/opt/mssql/bin/sqlservr"]

What this Dockerfile does:

• 
  Set up Base Image: Uses the latest SQL Server 2022 image from Microsoft’s container registry.


• 
  Environment Variables: Configures SQL Server to accept the license agreement (ACCEPT_EULA=Y).


• 
  Run Commands as Root: Switches to the root user to allow administrative tasks during the build.


• 
  Copy Files to Container: Copies the SQL backup file and database restore script into the container.


• 
  Set Permissions and Restore Database: Grants executable permissions to the restore script and restores the DemoDb database.
  
  
  
  
  • 
    Securely Access Password: Uses RUN --mount=type=secret,id=SA_PASSWORD to mount the SA_PASSWORD as a secret accessible only during the build step. When building the Docker image, we’ll pass the SA password using Docker BuildKit.
  
  
  • 
    Set SA_PASSWORD Variable: Reads the password securely with SA_PASSWORD=$(cat /run/secrets/SA_PASSWORD), making it available for the restore script without storing it in the final image.
  
  
  • 
    Clean Up: Removes the restore script and backup file after the database is restored to keep the image lean.
  
  
  
  


• 
  Define Entrypoint: Specifies SQL Server as the main process to start when the container runs.

Create a Database Restore Script

This script, pre-restore-db.sh, is run in the Dockerfile to restore the DemoDb database:

# Start SQL Server in the background

/opt/mssql/bin/sqlservr &



# Wait for SQL Server to come up

sleep 10s



# Restore DemoDb database

/opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" -C \

  -Q "RESTORE DATABASE DemoDb FROM DISK='/var/opt/mssql/DemoDb.bak' WITH MOVE 'DemoDb' TO '/var/opt/mssql/data/DemoDb.mdf', MOVE 'DemoDb_log' TO '/var/opt/mssql/data/DemoDb_log.ldf'" -C



# Stop SQL Server after the restoration is complete

kill $(pgrep sqlservr)

What this script does:

• Starts SQL Server in the background: Runs the SQL Server instance so that the restore command can connect to it.


• Waits for SQL Server to initialize: Pauses for a few seconds to ensure SQL Server is fully up and running..


• Restores the DemoDb database: Uses the sqlcmd tool to restore the DemoDb database from a .bak file located in the container, with data and log files moved to the appropriate directories.


• Stops SQL Server: After the restore process is complete, SQL Server is stopped to finalize the image build.

Setting Up Secrets for the Build

To securely pass the SA_PASSWORD for SQL Server without embedding it in the Dockerfile, we’ll use Docker BuildKit secrets. This method ensures the password is only accessible during the build process and is not stored in the final image layers.

Note: If security is not a primary concern for the password (e.g., it’s a temporary password only for building the Docker image), you could pass the password as an ARG in the Dockerfile.

1. 
   Create a Secret File: Create a file named sa_password.txt with your SQL Server password.


2. 
   Enable BuildKit from your terminal:
   
   
   
   
   • 
     For Unix-based environments (Linux, macOS) or Git Bash: Run export DOCKER_BUILDKIT=1
     
   
   
   • 
     For Windows PowerShell: Run $env:DOCKER_BUILDKIT = "1"
     
   
   
   
   

Build the Docker Image

Open a terminal in the directory containing the Dockerfile, along with the pre-restore-db.sh restore script, the database backup file, and the sa_password.txt file.

Run a Container from the Image

Use the docker run command to run the container using the image we just created.

For Unix-based environments (Linux, macOS) or Git Bash:

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=********' \

   --name 'demodb' -p 1401:1433 \

   -d demodb-1.0

For Windows PowerShell:

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

   --name "demodb" -p 1401:1433 `

   -d demodb-1.0

What this command does:

• Starts a container named 'demodb' with SQL Server accessible on port 1401 on your host machine.


• The MSSQL_SA_PASSWORD is passed to set the SA password for SQL Server in the live container.

We can see the container running with docker ps: