Create SQL Server database from a script in docker

Question

Simple question I hope. I cannot find anything anywhere.

How do you create a database in a Microsoft SQL Server Docker container?

Dockerfile:

I am looking at the following Dockerfile:

FROM microsoft/mssql-server-windows-developer:latest

ENV sa_password ab873jouehxaAGR

WORKDIR /
COPY /db-scripts/create-db.sql .

# here be the existing run commnd in the image microsoft/mssql-server-windows-developer:latest
#CMD ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';", ".\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose" ]

RUN (sqlcmd -S localhost -U SA -P ab873jouehxaAGR -i create-db.sql)

docker-compose.yml:

I have put together the following docker compose file:

version: '3.4'

services: 
  sql.data:
    image: ${DOCKER_REGISTRY}myfirst-mssql:latest
    container_name: myfirst-mssql_container
    build:
      context: .
      dockerfile: ./Dockerfile
    environment:
      SA_PASSWORD: ab873jouehxaAGR
      ACCEPT_EULA: Y

Bringing it together

I am running the command docker-compose up against the above. And assuming create-db.sql file will simply create a database which is not relevant here to keep things minimal.

Errors

The error I get above is that the login for SA is invalid when it runs the .sql script:

Step 7/7 : RUN (sqlcmd -S localhost -U SA -P ab873jouehxaAGR -i create-db.sql)
 ---> Running in 2ac5644d0bd9
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'SA'..

It looks like this runs before the password has been changed to ab873jouehxaAGR which typically looks like the command from mssql-server-windows-developer:latest.json from inspecting the image in vscode - \start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs actually does.

Environment

I am running docker Docker version 18.06.1-ce, build e68fc7a on Windows 10.

Attach or script

I am not specifying attaching a database using the environment variable attach_dbs of which I see in many examples.

I am trying to find a best practice for managing a sql container from a point of view of end to end testing and a lot of articles seem to not cover the data aspect part - ie Development Workflow

I would be interested to hear in comments thoughts on these two approaches in the Docker world.

user_8336519 · Answer

using following commands can solve your problem

docker-compose up --build -d

version: '3.4'
services: 
  sql.data:
    image: ${DOCKER_REGISTRY}myfirst-mssql:latest
    container_name: myfirst-mssql_container
    environment:
      SA_PASSWORD: ab873jouehxaAGR
      ACCEPT_EULA: Y

and after that:

docker exec myfirst-mssql_container sqlcmd 
  -d master 
  -S localhost
  -U "sa"
  -P "ab873jouehxaAGR"
  -Q 'select 1'

Create SQL Server database from a script in docker

1 Answers