Docker initialize database tables and records in SQL Server

Question

How do you specify initialization database script for SQL Server via docker-compose using SQL script file?

docker-compose.yml

database:
    image: microsoft/mssql-server-linux:2017-latest
    container_name: database
    ports:
      - 1433:1433
    volumes:
      - /var/opt/mssql
    environment:
      SA_PASSWORD: "P@55w0rd"
      ACCEPT_EULA: "Y"

schema.sql

CREATE TABLE Department
(
    Id INT PRIMARY KEY IDENTITY (1, 1),
    Name VARCHAR (50) NOT NULL
);

CREATE TABLE Student
(
    Id INT PRIMARY KEY IDENTITY (1, 1),
    Name VARCHAR (50) NOT NULL
);

CREATE TABLE Course 
(
    Id INT PRIMARY KEY IDENTITY (1, 1),
    Name VARCHAR (50) NOT NULL
);

INSERT INTO Student (Id, Name) VALUES(1, "John Doe");
INSERT INTO Student (Id, Name) VALUES(2, "Jane Doe");

2023c , Any update? The official repository closed an initiative github.com/microsoft/mssql-docker/pull/60 — JRichardsz
– JRichardsz, Commented May 9, 2023 at 14:59

Louis Stekhoven-Smith · Accepted Answer · 2021-09-28 04:51:16Z

14

For those who like a little more specifics/examples.

As @Adiii suggested this is how you can initialise the database within a docker-compose file

docker-compose.yml

  test-database:
    build: ./mssql_database
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=<YOUR PASSWORD>
      - TZ=UTC
    volumes:
      - database-data:/var/opt/mssql

volumes:
    database-data:

Directory mssql_database contains

Dockerfile

FROM mcr.microsoft.com/mssql/server

COPY setup.sql setup.sql
COPY setup_database.sh setup_database.sh
COPY entrypoint.sh entrypoint.sh

CMD /bin/bash ./entrypoint.sh

NOTE: If you want to initialise the database during build time instead of run time replace the CMD /bin/bash ./entrypoint.shwith RUN ./opt/mssql/bin/sqlservr & ./setup_database.sh

entrypoint.sh

#!/usr/bin/env bash
set -m
./opt/mssql/bin/sqlservr & ./setup_database.sh
fg

setup_database.sh

NOTE: Instead of using a sleep it would be better to use a healthcheck and once the database is confirmed green run your scripts.

#!/usr/bin/env bash
# Wait for database to startup 
sleep 20
./opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P <YOUR PASSWORD> -i setup.sql

setup.sql

INSERT your sql commands here

edited Sep 28, 2021 at 4:51

answered Jan 29, 2020 at 6:38

Louis Stekhoven-Smith

1,25915 silver badges11 bronze badges

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

2 Comments

Ryan Vice Over a year ago

I think you need to add ``` volumes: database-data: ``` to the docker-compose file

Louis Stekhoven-Smith Over a year ago

Sure I can add that for completeness

Adiii · Accepted Answer · 2019-10-09 18:01:30Z

4

If you check the offical documentataion, it suggests to use mssql-docker-demo-app which contain entrypoint script like MySQL container.

import-data.sh

The import-data.sh script is a convenient way to delay the execution of the SQL commands until SQL Server is started. Typically SQL Server takes about 5-10 seconds to start up and be ready for connections and commands.

The next command uses the SQL Server command line utility sqlcmd to execte some SQL commands contained in the setup.sql file.

The setup.sql script will create a new database called DemoData and a table called Products in the default dbo schema.

setup.sql

The setup.sql defines some simple commands to create a database and some simple schema. You could use a .sql file like this for other purposes like creating logins, assigning permissions, creating stored procedures, and much more. When creating a database in production situations, you will probably want to be more specific about where the database files are created so that the database files are stored in persistent storage. This SQL script creates a table with two columns -

ID (integer) and ProductName (nvarchar(max)).

CREATE DATABASE DemoData;
GO
USE DemoData;
GO
CREATE TABLE Products (ID int, ProductName nvarchar(max));
GO

edited Oct 9, 2019 at 18:01

answered Oct 9, 2019 at 17:50

Adiii

61k10 gold badges179 silver badges173 bronze badges

8 Comments

AppDeveloper Over a year ago

What is the meaning of GO statement, so it has to be after each SQL Statement?

Adiii Over a year ago

Yes may be it seems like that from script but I'll prefer to check the repo documentation

AppDeveloper Over a year ago

they added a delay of 90s, don't you think that's too much.

Adiii Over a year ago

They mentioned the reason too, so consider it official recommendation

AppDeveloper Over a year ago

launch is successful, but as soon as following SQL commands execute, the container just crash, CREATE DATABASE DemoData; GO USE DemoData; GO

|

yuranos · Accepted Answer · 2020-07-27 12:27:29Z

A note if you do use docker-compose and not just bare docker. A snippet from my docker-compose file:

  sql-server:
    image: mcr.microsoft.com/mssql/server:2017-latest-ubuntu
    hostname: sql-server
    container_name: sql-server
    ports:
    - 1433:1433
#    Based on: https://www.softwaredeveloper.blog/initialize-mssql-in-docker-container or similarly: https://github.com/twright-msft/mssql-node-docker-demo-app
    command: bash -c "chmod -R 777 /usr/src/sql/ && /usr/src/sql/entrypoint.sh"
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Password123
      MSSQL_PID: Express
    volumes:
      - ./sql/:/usr/src/sql/

Now, since I don't use custom Dockerfile I'm obviously not mangling with workdir. And when I run:

docker inspect mcr.microsoft.com/mssql/server:2017-latest-ubuntu | grep -i "DIR"

I get:

    "WorkingDir": "",
    "WorkingDir": "",

Meaning working dir is not set and so you land in the root folder inside the container by default.

This will make scripts like: ./opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P <YOUR PASSWORD> -i setup.sql non-functioning because there's no setup.sql in the root dir. In that case you need to specify absolute path like:

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SA_PASSWORD} -d master -i /usr/src/sql/setup.sql

miken32 · Accepted Answer · 2025-03-17 16:53:12Z

1

I don't know why there is no official documentation from Microsoft about this feature, but while trying to understand mssql image, I found an out-of-the-box solution.

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "password123!"
    ports:
      - "1433:1433"
    healthcheck:
      test: ["CMD-SHELL", "/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P $$MSSQL_SA_PASSWORD -C -Q 'SELECT 1'"]
      start_period: 30s
      interval: 1s
      timeout: 1s
      retries: 1
    volumes:
      - ./scripts/:/mssql-server-setup-scripts.d/

The key point is the mount local folder scripts to folder /mssql-server-setup-scripts.d/ that contains initialization **.sql scripts.

edited Mar 17 at 16:53

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Mar 17 at 16:30

Nguyễn Kỳ Dương Trường

3381 silver badge7 bronze badges

2 Comments

James Oct 1 at 12:57

Where did you find this information? It worked for me

Nguyễn Kỳ Dương Trường Oct 2 at 10:55

I read logs of the container while I created it freshly, and figured it out.

JRichardsz · Accepted Answer · 2023-12-25 13:42:25Z

0

Based on the official image, I created this:

https://hub.docker.com/r/usildevops/mssql-docker-enhanced

In which I added the classic /docker-entrypoint-initdb.d and a tested health check

mssql-docker-enhanced:
    image: usildevops/mssql-docker-enhanced
    container_name: mssql-docker-enhanced
    shm_size: 1g
    ports:
     - "1433:1433"
    environment:
      ACCEPT_EULA: 1
      MSSQL_PID: Developer
      MSSQL_USER: sa
      MSSQL_SA_PASSWORD: changeme
      TZ: America/Lima
    volumes:
      - ./my/scripts:/docker-entrypoint-initdb.d
    networks:
      - template_network
    healthcheck:
      test: cat /var/log/docker/sqlserver_db_init.log | grep [db_init_completed]
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s

edited Dec 25, 2023 at 13:42

answered May 11, 2023 at 0:17

JRichardsz

16.9k9 gold badges70 silver badges118 bronze badges

Comments

Daniel Ribeiro · Accepted Answer · 2022-01-15 15:35:31Z

Another way of doing it, without having to build your own image (no Dockerfile):

# [Create and] Run the MSSQL container
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<sa_password>" --name <container_id> -p 1433:1433 -d mcr.microsoft.com/mssql/server

# Create a "sqlsetup" directory inside the container
docker exec -it <container_id> mkdir /usr/sqlsetup

# Copy your database setup script to the container
docker cp setup.sql <container_id>:/usr/sqlsetup/setup.sql

# Run the script on the database
docker exec -it <container_id> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <sa_password> -d master -i /usr/sqlsetup/setup.sql

Do not run all the commands at once. SQL Server takes some time to be up and running, so you need some delay to allow it to be running before trying to execute the script.

Delcroip · Accepted Answer · 2022-05-11 07:13:22Z

I had the the same issue, I got inspired by this post https://www.softwaredeveloper.blog/initialize-mssql-in-docker-container

but I made a script that init the database only if not existing: 1- I COPY the sql and sh script in the dockerfile 2- I change the command in the DockeFile: CMD /bin/bash ./entrypoint.sh 3- I create an entrypoint.sh that will call the init script and start the server 4- I create my init script

#!/bin/bash
# Wait to be sure that SQL Server came up
sleep 30s
# DATABSE initialisation
echo "Database initialisation"
# if the table does not exsit it will create the table
# get "1" if the database exist : 'tr' get only the integer, 'cut' only the first integer (the second is the number of row affected)
data=$(/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "SELECT COUNT(*)  FROM master.dbo.sysdatabases WHERE name = N'$DB_NAME'" | tr -dc '0-9'| cut -c1 )
if [ ${data} -eq "0" ]; then
        echo 'create database user'
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "CREATE LOGIN $DB_USER WITH PASSWORD='${SA_PASSWORD}', CHECK_POLICY = OFF"

        echo 'create database'
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "DROP DATABASE IF EXISTS $DB_NAME"
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "CREATE DATABASE $DB_NAME"
        # RUN YOUR SQL FILE HERE

        echo ' give to the user the access to the database'
        /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "CREATE USER $DB_USER FOR LOGIN $DB_USER " -d $DB_NAME
else
        echo "database already existing, nothing to do"
fi

# manual cleaning command
# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "DROP DATABASE $DB_NAME"
# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "DROP  LOGIN  $DB_USER"

I hope it can help someone, you can find the github repo where I did it here : https://github.com/openimis/openimis-db_dkr/blob/release/202204/Dockerfile

I use the same approach for the health check:

data=$(/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $SA_PASSWORD -Q "SELECT COUNT(*)  FROM master.dbo.sysdatabases WHERE name = N'$DB_NAME'" | tr -dc '0-9'| cut -c1 )
if [ ${data} -eq "1" ]; then
    exit 0
else
    exit 1
fi

Collectives™ on Stack Overflow

Docker initialize database tables and records in SQL Server

7 Answers 7

For those who like a little more specifics/examples.

2 Comments

8 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

For those who like a little more specifics/examples.

2 Comments

8 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related