How to create MSSQL Image With Custom DBs Attached (using MDF/LDF files)

If MS SQL Server is running on Windows Image and the environment variable attach_dbs is detected, then it will automatically try and attach the databases defined there.

The following Dockerfile can be used to create the custom image, using a local context.

Note: This uses the MSSQL base image shown here.

The example shows how to add one custom database called Audit. The corresponding Audit.mdf and Audit_log.ldf need to be in the same folder as the docker file:

Dockerfile

FROM deanobrien/sql2019:empty

ENV sa_password="xxx" \
    ACCEPT_EULA="Y" \
    sa_password_path="C:\ProgramData\Docker\secrets\sa-password" \
    attach_dbs='[{"dbName":"Audit","dbFiles":["c:\\Custom\\Audit.mdf","c:\\Custom\\Audit_log.ldf"]}]'
SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

RUN mkdir C:/Custom/

# make install files accessible
COPY Audit.mdf C:/Custom 
COPY Audit_log.ldf C:/Custom 

WORKDIR /

HEALTHCHECK CMD [ "sqlcmd", "-Q", "select 1" ]

CMD .\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

Build using docker build command

To create the image, you need to save the above as a file called Dockerfile and run the following command:

 docker build -t deanobrien/sql2019:audit .

Where ‘deanobrien’ is the name of the repository where we will store the resulting image.

You would then need to push the image manually to the registry.

Build using azure command

You can also push the local context to azure and build directly in the registry. To do this, navigate to the folder with the above Dockerfile and run the following command:

build-custom-image.ps1

az login
az account set --subscription xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx
az acr login
az acr build -t deanobrien/sql2019:audit -r deanobrien . --platform windows

If successful, the above command will push the resulting image to the Azure Container Registry. Which is where our final Kubernetes solution will call the image down from.

Add label