Docker for windows sql express volume data does not persist

Question

I'm having difficulty getting my mssql-server-windows-express containerized database to retain any created rows after restarting my computer.

It resides in a project that I created with Visual Studio 2017 Community Edition's default "Enable Docker Support" checkbox. I am using Entity Framework Core 2 migrations to create and update the database with ASP.NET Core 2.1.

I feel like the problem might have to do with Docker for Windows having an usual path syntax, but that's just a hunch.

I have tried so many different Stackoverflow and blog suggestions and paths, but the data never persists. I have to ask for help at this point.

Here are two out of the many docker-compose files and ideas I have tried:

version: '3.4'

services:
  webpresentation:
    image: webpresentation
    build:
      context: .
      dockerfile: WebPresentation\Dockerfile

  db:
    image: microsoft/mssql-server-windows-express
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Test1

    ports:
      - "1433:1433"
    volumes:
      - "C:\ProgramData\Docker\volumes\dockercompose4363425345347741_sqlvolume:c:\var/opt/mssql"

volumes:
  sqlvolume:

This file compiles, but the rows don't persist after restarting.

version: '3.4'

services:
  webpresentation:
    image: webpresentation
    build:
      context: .
      dockerfile: WebPresentation\Dockerfile

  db:
    image: microsoft/mssql-server-windows-express
    container_name: myDbSqlExpress
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Test1
      'attach_dbs={"dbName":"MyDb","dbFiles":"C:\ContainerData\MyDb.mdf","C:\ContainerData\MyDb.ldf"}'


    ports:
      - "1433:1433"
    volumes:
      - "C:\DockerData:c:\ContainerData"

volumes:
  sqlvolume:
     name: myDb-sqlvolume

This file gives an error:

Error MSB4018 The "GetServiceReferences" task failed unexpectedly. Microsoft.Docker.Utilities.CommandLineClientException: yaml.scanner.ScannerError: while scanning a simple key in "C:\Projects\MyProject\MyApp\docker-compose.yml", line 16, column 7 could not find expected ':'

What am I doing wrong here? Can anyone help me correct either file so the newly created rows persist after restarting the computer?

Thanks very much!

user_2085502 · Answer

This is not a real answer but too long for a comment, so...

There are two versions of mssql docker container: windows and Linux. You are using the windows container while Linux containers are more common. This is why many examples in the Internet will not work as expected.

In your first example it seem that you're using the path used by mssql for Linux (/var/opt is a typical Linux path). So 1) find the path that is used inside your container to store db data.

Your second example is simply no valid yaml. The syntax is key: value so your attach_db line is invalid.

From docs:

ENV attach_dbs='[{"dbName":"mydb","dbFiles":["C:\temp\mydb.mdf","C:\temp\mydb.ldf"]}]'

Here we see that it's an environment variable. You set env vars like this:

env:
  attach_dbs: '[{"dbName":"mydb","dbFiles":["C:\temp\mydb.mdf","C:\temp\mydb.ldf"]}]'

This should fix your parse error.

Than you should read about volumes here: https://docs.docker.com/compose/compose-file/#volumes

If you specify a named volume with a separate "volumes" section, the syntax is name:path_inside_container.

One more note: I'd go with Linux containers. You'll find a menu item "Switch to Linux containers..." in you docker for windows menu (tray icon). If this is disabled, you may have to install Linux from Microsoft store (simply search for ubuntu).

user_214956 · Answer

The docker-compose.yml file that eventually allowed me to persist data after system restarts is as follows:

version: '3.4'

services:
  webpresentation:
    image: webpresentation
    build:
      context: .
      dockerfile: WebPresentation\Dockerfile

  db:
    image: microsoft/mssql-server-windows-express
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Test1
      attach_dbs: '[{"dbName":"MyProject","dbFiles":["C:\DockerDbData\MyProject.mdf","C:\DockerDbData\MyProject_log.ldf"]}]'

    ports:
      - "1433:1433"
    volumes:
      - "C:\Projects\MyProject\DockerDbDataVolume:C:\DockerDbData"

Docker for windows sql express volume data does not persist

2 Answers