Introduction

Unlike MySQL and PostgreSQL, which execute scripts in the /docker-entrypoint-initdb.d/ directory after initialization, the SQL Server container image doesn’t provide a built-in mechanism for running scripts at startup.

In this post, I’ll show how to create a custom container image that waits for SQL Server startup and runs a pre-configured script. In addition, I’ll show how to use this custom image to run SQL Server in Testcontainers.

See GitHub Issue #892 for details.

How to run scripts on SQL Server initialization

The SQL Server Docker image documentation references this code as an example of how to run scripts on initialization, but the script is outdated and doesn’t work.

I opened a Pull Request with a fixed version. It was never merged, but the fixes are incorporated into the approach below.

Below, I’ll explain each of the files.

1 - Dockerfile

The Dockerfile starts from the SQL Server 2022 image, copies all files into /tmp/initscripts, and sets the container entrypoint to entrypoint.sh.

FROM mcr.microsoft.com/mssql/server:2022-latest

# Bundle config source
COPY . /tmp/initscripts

ENTRYPOINT ["/tmp/initscripts/entrypoint.sh"]

2 - entrypoint.sh

This bash script launches configure-db.sh in the background, then starts SQL Server in the foreground. Using exec ensures the SQL Server process replaces the shell as PID 1, so it receives Docker stop signals directly.

#!/bin/bash

# Start the script to create the DB and user
/tmp/initscripts/configure-db.sh &

# Start SQL Server (exec replaces shell with sqlservr as PID 1)
exec /opt/mssql/bin/sqlservr

3 - configure-db.sh

This is the most important part. The script waits up to 60 seconds (TRIES variable) for SQL Server to start and all databases to reach ONLINE state, then executes setup.sql. On success it logs Configuration completed.; on timeout it exits with code 1.

#!/bin/bash

# Calls SQLCMD to verify that system and user databases return "0" which means all databases are in an "online" state,
# then run the configuration script (setup.sql)
# https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017 

TRIES=60
DBSTATUS=1
ERRCODE=1
i=0

while [[ $DBSTATUS -ne 0 ]] && [[ $i -lt $TRIES ]]; do
	i=$((i+1))
	DBSTATUS=$(/opt/mssql-tools18/bin/sqlcmd -h -1 -t 2 -C -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -Q "SET NOCOUNT ON; Select COALESCE(SUM(state), 0) from sys.databases") || DBSTATUS=1
	
	sleep 1s
done

if [ $DBSTATUS -ne 0 ]; then 
	echo "SQL Server took more than $TRIES seconds to start up or one or more databases are not in an ONLINE state"
	exit 1
fi

# Run the setup script to create the DB and the schema in the DB
echo "Running configuration script..."

/opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -d master -i setup.sql

echo "Configuration completed."

4 - setup.sql

This is the script that will be run. Here I’m just creating the database.

CREATE DATABASE [MyDatabase]
GO

5 - Building the container image

Now, we have to build the container image from the Dockerfile we created. In this example, I’m naming the image mydatabase-sqlserver:

docker build -t mydatabase-sqlserver .

6 - Starting the container

Finally, we can run the container with docker run:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 mydatabase-sqlserver

Or create a compose.yaml file:

services:
  sqlserver:
    build: .
    environment:
      MSSQL_SA_PASSWORD: "yourStrong(!)Password"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "Developer"
    ports:
      - "1433:1433"

And run:

docker compose up

Source code of this example

• https://github.com/dgenezini/SqlServerScriptOnStartup

Running the configuration script in Testcontainers

Testcontainers is a library that provides lightweight, throwaway instances of databases, selenium web browsers, or anything that can run in a container. These instances can be especially useful for testing applications against real dependencies, like databases, that can be created and disposed of after the tests.

I explained about Testcontainers in this post.

I strongly recommend reading it first. Here, I’ll cover only the specifics of running SQL Server with configuration scripts in Testcontainers.

Starting the container

When starting the container, we need to override two configurations in MsSqlBuilder:

• WithImage overrides MsSqlBuilder’s default image with our custom image (mydatabase-sqlserver)
• WithWaitStrategy overrides the default wait strategy with UntilMessageIsLogged. This makes StartAsync block until Configuration completed appears in the container logs, ensuring the setup script finishes before we use the container.

1
2
3
4
5
6
7

var MsSqlContainer = new MsSqlBuilder()
    .WithImage("mydatabase-sqlserver")
    .WithPassword("SqlServer2022!Password")    
    .WithWaitStrategy(Wait.ForUnixContainer().UntilMessageIsLogged("Configuration completed"))
    .Build();

await MsSqlContainer.StartAsync();

Source code of this example

• https://github.com/dgenezini/TestcontainersSqlServerSample