Create database on docker-compose MS SQL

Question

I would like to create a MS SQL database using environment variables in docker-compose.yml file :

 version: "3"

services:
  database:
    restart: always
    image: mcr.microsoft.com/mssql/server
    container_name: ms-sql-server-latest
    volumes:
    - ./init-script.sql:/docker-entrypoint-initdb.d/init-script.sql
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=password
    ports:
      - 14033:1433

My init-script.sql

CREATE DATABASE IF NOT EXISTS some_name;
   USE some_name;

But is does not created the database some_name.

CREATE DATABASE IF NOT EXISTS is not valid tsql syntax. You can use this instead: IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name='some_name') CREATE DATABASE some_name; — squillman
– squillman, Commented Nov 4, 2021 at 14:59
Possibly docker-entrypoint-initdb.d/init-script.sql isn't supported by SQL Server - I think it's a Postgres-only feature — Ed Harper
– Ed Harper, Commented Nov 4, 2021 at 15:04

dmartinezfernandez · Accepted Answer · 2023-08-20 20:54:18Z

0

Follow this example: https://github.com/microsoft/mssql-docker/tree/master/linux/preview/examples/mssql-customize

MSSQL docker image doesn't have a directory "docker-entrypoint-initdb.d" as PostgreSQL have.

answered Aug 20, 2023 at 20:54

dmartinezfernandez

512 silver badges4 bronze badges

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

Comments

The Lemon · Accepted Answer · 2025-04-16 06:02:42Z

I wish there were a utility this simple for it. Unfortunately there is an extra step to make (at least for SQL Server)

Your compose needs to add an entry point file, this is what we use.

  my_db_container:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "Hey!Don't look here. Bad!"
    ports:
      - "1433:1433"
    networks:
      - dev_network
    volumes:
      - ./db-init/init.sql:/db/init.sql
      - ./db-init/entrypoint.sh:/entrypoint.sh
    entrypoint: ["/bin/bash", "/entrypoint.sh"]

First our docker-compose sets up the db image. The networks: - dev_network is to make it easier to reach my db container from my app container. The 'volumes' are how my container has access to the scripts it needs to create the db. In my docker-compose project I also need to have the two files specified in 'volumes' (under db-init)

init.sql - just needs to be a bit more involved. Here is my script where some_name = Jeff

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'Jeff')
BEGIN
    CREATE DATABASE [Jeff];
END

entrypoint.sh. This is the one that I had to fight with as mssql_tools can have different names depending on your image. If it fails just verify /opt/mssql-tools18/bin/sqlcmd is where you expect by looking at the file system through docker desktop. In the below case, we set the entry point to wait until sql is live, then run our init script.

#!/bin/bash

# Start SQL Server in the background
/opt/mssql/bin/sqlservr &

# Wait until SQL Server is ready
echo "Waiting for SQL Server to be available..."
until /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -C -Q "SELECT 1" > /dev/null 2>&1; do
  sleep 1
  echo "Still Waiting for SQL Server to be available..."
done

# Run your init script
echo "Running init.sql..."
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -C -i /db/init.sql
echo "Finished running init.sql."
# Keep container alive
wait

Collectives™ on Stack Overflow

Create database on docker-compose MS SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related