Run SQL script after start of SQL Server on docker

Question

I have a Dockerfile with below code

FROM microsoft/mssql-server-windows-express
COPY ./create-db.sql .
ENV ACCEPT_EULA=Y
ENV sa_password=##$wo0RD!
CMD sqlcmd -i create-db.sql

and I can create image but when I run container with the image I don't see created database on the SQL Server because the script is executed before SQL Server was started.

Can I do that the script will be execute after start the service with SQL Server?

Dropsy

You can run the script after starting Docker and connecting to the SQL Server on the Docker container using SSMS from the host - your PC. Or you need to do this dynamically? – Mitchiner

I wanted to do it dynamically, because I want to have SQL Server with concrete database immediately after ran container – Dropsy

Answer 1

RUN gets used to build the layers in an image. CMD is the command that is run when you launch an instance (a "container") of the built image.

Also, if your script depends on those environment variables, if it's an older version of Docker, it might fail because those variables are not defined the way you want them defined!

In older versions of docker the Dockerfile ENV command uses spaces instead of "="

Your Dockerfile should probably be:

FROM microsoft/mssql-server-windows-express
COPY ./create-db.sql .
ENV ACCEPT_EULA Y
ENV SA_PASSWORD ##$wo0RD!
RUN sqlcmd -i create-db.sql 

This will create an image containing the database with your password inside it.

(If the SQL file somehow uses the environment variables, this wouldn't make sense as you might as well update the SQL file before you copy it over.) If you want to be able to override the password between the docker build and docker run steps, by using docker run --env sa_password=##$wo0RD! ..., you will need to change the last line to:

CMD sqlcmd -i create-db.sql && .\start -sa_password $env:SA_PASSWORD \
-ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

Which is a modified version of the CMD line that is inherited from the upstream image.

Rodmur

Ok I changed it and when I build image and run the image I see container about 1 min and after the next 1 min container is stopped – Dropsy

When I changed CMD to RUN it started working correctly and it works for both my version Dockerfile :). In first version I have script with creating db from scratch and in second version I have restore db from bak files. Option with CMD I would not use it because I don't have any option to get files mdf and ldf I can have only scripts or backup files. Thank you very much for your help :) – Dropsy

I am currently doing this, by running an entrypoint script, after I ran the docker container (Using mcr.microsoft.com/mssql/server:2017-latest). However, seeding data only once when building is a much better solution. Only problem, the database doesnt seem accessible. I always get: Step 5/5 : RUN /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 13j1k23j1l3H! -d master -i db-init.sql ---> Running in 57452421d9d4 Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired. – Oddson

Makes sense, considering that the service is probably not up, right? How can I solve this? – Oddson

@Oddson did you manage to solve this issue? I am currently encountering the same. – Foresee

Check your CMD... if it just runs and exits, your container will just run and exit. – Rodmur

By the way SA_PASSWORD must be uppercase. Lowercase didn't work for me. – Parental

Answer 2

You can follow this link https://github.com/microsoft/mssql-docker/issues/11. Credits to Robin Moffatt. Change your docker-compose.yml file to contain the following

mssql:
image: microsoft/mssql-server-windows-express
environment: 
  - SA_PASSWORD=##$wo0RD!
  - ACCEPT_EULA=Y
volumes:
  # directory with sql script on pc to /scripts/
  # - ./data/mssql:/scripts/
  - ./create-db.sql:/scripts/
command:
  - /bin/bash
  - -c 
  - |
    # Launch MSSQL and send to background
    /opt/mssql/bin/sqlservr &
    # Wait 30 seconds for it to be available
    # (lame, I know, but there's no nc available to start prodding network ports)
    sleep 30
    # Run every script in /scripts
    # TODO set a flag so that this is only done once on creation, 
    #      and not every time the container runs
    for foo in /scripts/*.sql
      do /opt/mssql-tools/bin/sqlcmd -U sa -P $$SA_PASSWORD -l 30 -e -i $$foo
    done
    # So that the container doesn't shut down, sleep this thread
    sleep infinity

Welby

Answer 3

If you just want to only seed the database initially during build and then have it immediately available in each container you create from the image, you can do something like this:

FROM mcr.microsoft.com/mssql/server
COPY setup.sql .
ENV MSSQL_PID=Express
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD="=verysecurepassword"
RUN /opt/mssql/bin/sqlservr & sleep 10 && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P verysecurepassword -i setup.sql

Destructive