SQL Server Docker Compose SQLCMD does not execute

Question

I'm currently trying to set up a SQL Server in docker compose and I want to create the database on build with the RUN instruction. This doesn't work, however when I execute the same command on the running container with sh, it works

my compose file looks like this:

version: "3.7"

services:
  mssql:
    build: ./mssql
    environment: 
      SA_PASSWORD: "Password12345!"
      ACCEPT_EULA: "Y"
    container_name: mssqlDB
    ports:
      - "1433:1433"
    restart: always

And here my Dockerfile:

FROM mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
COPY ./prod.sql /
RUN ./opt/mssql-bin/sqlcmd -S localhost -U SA -P "Password12345!" -Q "Create Database HelloWorld"
CMD ["/opt/mssql/bin/sqlservr"]

gbeaven · Accepted Answer · 2024-06-01 01:30:43Z

7

This is because the SQL Server instance is not started and you must wait for it.

From the Docker Hub official page of SQL Server there is a link to a GitHub Repository that shows how to run a sql script on Docker container.

Below I have re-adapted the GitHub code for you case

initialize.sh

# Typically SQL Server takes about 5-10 seconds to start up 
# Wait for the SQL Server to come up (90 sec) You can reduce to 20sec and see
sleep 90s

#run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P password -d master -i prod.sql

entrypoint.sh

#start SQL Server, start the script to create the DB and import the data
/opt/mssql/bin/sqlservr & initialize.sh

Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
COPY ./prod.sql /

# Grant permissions for the import-data script to be executable
RUN chmod +x ./initialize.sh

CMD /bin/bash ./entrypoint.sh

Another solution that I personally made is to run the SQL Server service and wait until the service came up.

create.sh

/opt/mssql-tools/bin/sqlcmd -U sa -P $1 -Q 'CREATE DATABASE [MyNewDatabase]'
/opt/mssql-tools/bin/sqlcmd -U sa -P $1 -d 'MyNewDatabase' -i /src/script.sql

script.sql

CREATE TABLE MyTable (..)

DockerFile


FROM mcr.microsoft.com/mssql/server:2017-latest-ubuntu
EXPOSE 1433

WORKDIR /
COPY ./create.sh /src/
COPY ./script.sql /src/

ENV ACCEPT_EULA Y
ENV SA_PASSWORD P@ssw0rd

RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
    && /src/create.sh P@ssw0rd \
    && pkill sqlservr

edited Jun 1, 2024 at 1:30

gbeaven

1,8302 gold badges23 silver badges48 bronze badges

answered Dec 10, 2019 at 9:23

Max

7,1503 gold badges51 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

exrezzo Over a year ago

man you saved my day! I don't know why but I was trying to create and use the db from within the sql script but it didn't work, eventually I used your solution where db creation e inserts are split in 2 bash commands and it worked. I used the second solution because it's clean and doesn't wait for an arbitrary time span. Thanks a lot for your solution! ❤️

Max Over a year ago

@exrezzo Glad to help you! I'm Italian like you!

Justinas Over a year ago

What's the syntax to pass password var to password section? /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P password -d master -i prod.sql Because in my case /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -d master -i ./setup.sql I get errror what Password did not match that for the login provided.

Collectives™ on Stack Overflow

SQL Server Docker Compose SQLCMD does not execute

1 Answer 1

initialize.sh

entrypoint.sh

Dockerfile

create.sh

script.sql

DockerFile

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

initialize.sh

entrypoint.sh

Dockerfile

create.sh

script.sql

DockerFile

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related