Search code examples
dockerdocker-composedockerfile

Restore a SQL Server DB.bak in a Dockerfile

I am running a .NET Razor application, an instance of gitea, and a SQL Server database each in separate containers that communicate with one another. I would like to start my database image with a database schema and data (by restoring a .bak file).

I can do this with my current Dockerfile, if once it is up and running, I run these additional commands:

  1. docker exec -it myContainer /opt/mssql-tools/bin/sqlsmd -S localhost -U sa -P myPassword

  2. /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P myPassword -Q "RESTORE DATABASE MY_DB_NAME FROM DISK='/var/opt/mssql/backup/MY_DB_NAME.bak' WITH MOVE 'MY_DB_NAME_TEST' TO '/var/opt/mssql/data/MY_DB_NAME_TEST.mdf', MOVE 'MY_DB_NAME_TEST_log' TO '/var/opt/mssql/data/MY_DB_NAME_TEST_log.ldf'"

This gets the job done, but I want to fully automate the process so that this is configured 100% by my docker-compose.yml and Dockerfile so I need only type: docker-compose up -d.

I don't think the content of my docker-compose.yml file is relevant, but here is my Dockerfile (where I am trying to run that script that I currently need to run after docker-compose up):

FROM microsoft/mssql-server-linux

ENV SA_PASSWORD=myPassword
ENV ACCEPT_EULA=Y

COPY ./ACES_DB.bak /var/opt/mssql/backup/MY_DB_NAME.bak
RUN docker exec -it myContainer bin/sh /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P myPassword -Q "RESTORE DATABASE MY_DB_NAME FROM DISK='/var/opt/mssql/backup/MY_DB_NAME.bak' WITH MOVE 'MY_DB_NAME_TEST' TO '/var/opt/mssql/data/MY_DB_NAME_TEST.mdf', MOVE 'MY_DB_NAME_TEST_log' TO '/var/opt/mssql/data/MY_DB_NAME_TEST_log.ldf'"

Any help would be much appreciated.

Solution

  • A friend and I puzzled through this together and eventually found this solution. Here's what the docker file looks like:

    FROM microsoft/mssql-server-linux
ENV MSSQL_SA_PASSWORD=myPassword
ENV ACCEPT_EULA=Y

COPY ./My_DB.bak /var/opt/mssql/backup/My_DB.bak
COPY restore.sql restore.sql
RUN (/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Starting database restore" && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'myPassword' -d master -i restore.sql

    *Note that I moved the SQL restore statement to a .sql file.