4

I'm trying to run a SQL Server Database in a container and have been unable to mount a volume using docker-compose. I am able to persist data using docker run, however.

SETUP

docker volume ls

DRIVER              VOLUME NAME
local               db-data-system
local               db-data-user local              
docker-containers_db-data-system local              
docker-containers_db-data-user

RUNNING DOCKER FROM COMMAND LINE

I'm able to run a container using docker run that persists data (see testdatabase and arbq_app).

sudo docker run -e 'ACCEPT_EULA=Y' -e 'P@ssw0rd' \
  --name 'arbq-db' -e 'MSSQL_PID=Developer' -p 1401:1433 \
  -v db-data-system:/var/opt/mssql -v db-data-user:/var/opt/sqlserver \
  -d mcr.microsoft.com/mssql/server:2017-latest

In dBeaver: SELECT name FROM sys.databases

master
tempdb
model
msdb
testdatabase
arbq_app

WITH DOCKER COMPOSE

docker-compose.yml:

version: '3.6'

services:

  # Another Container
  another-container:
    content: some-content 

  # SQL Server
  arbq-db:
    container_name: arbq-db
    image: mcr.microsoft.com/mssql/server:2017-latest
    ports:
     - "1401:1433"
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: P@ssw0rd
      MSSQL_PID: Developer
    volumes:
      - db-data-user:/var/opt/sqlserver
      - db-data-system:/var/opt/mssql

volumes:
  db-data-user:
  db-data-system:

I run docker-compose up --build

In dBeaver: SELECT name FROM sys.databases

master
tempdb
model
msdb

docker inspect -f '{{ .Mounts }}' e02bd89ccc38

[{volume docker-containers_db-data-user /var/lib/docker/volumes/docker-containers_db-data-user/_data /var/opt/sqlserver local rw true } {volume docker-containers_db-data-system /var/lib/docker/volumes/docker-containers_db-data-system/_data /var/opt/mssql local rw true }]

I honestly don't know what docker-containers_db-data-user and docker-containers_db-data-system are, but they don't contain the db data I need persisted!

What can I do to ensure that the testdatabase and arbq_app databases persist in the container when using docker-compose?

Thanks in advance.

2

If you let docker-compose manage the volumes for you, the volumes names in docker-compose.yml will not be the final ones created/used in docker. docker-compose will prepend the volumes names with the compose project name, which is by default the name of the folder holding the compose file. This is to allow separate projects to use the same names without conflict. For information, you will get the same naming convention for networks.

To get your data back correctly in your compose project, you have two choices.

Prefered in your case: Move data to automated volumes.

This will preserve the default compose volume management in your future runs.

  • stop any running container using any of those volumes
  • launch a dummy container not having mysql (e.g. busybox) and mount all 4 volumes in separate identifiable paths
  • empty all volumes created by docker-compose
  • copy all data from "docker only" volumes to "compose" volumes.
  • stop and recycle your dummy container.
  • docker-compose up -d and enjoy.
  • if all went good and smooth, delete the other unneeded volumes.

If you know what your doing: use external volumes

This will work as well but please check the documentation (Ctrl-f "Volume configuration reference") for details. Specifically, be aware that docker-compose up -d will no longer try to create the unexisting volumes and fire an error and stop if they don't exist.

Simply change you volume section at the end of your file to:

volumes:
  db-data-user:
    external:
      name: db-data-user
  db-data-system:
    external:
      name: db-data-system
  • Thanks! This is exactly what I needed. – David Thompson Jul 10 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.