CODEWITHSUNDEEP
sql-serverdockerdocker-compose
noblerare
noblerare

Reputation: 11933

Attaching SQL database in container does not work

I am having trouble getting a SQL database instance to attach to Docker container.

Here is my Dockerfile.

FROM microsoft/mssql-server-windows-express
EXPOSE 1433

WORKDIR C:\\tmp

COPY MyDBNAME.mdf ./
COPY MyDBNAME_log.ldf ./

WORKDIR /

I use that to build an image: docker build . --tag=mynewsql.

At this point, the mdf and ldf files are in the C:\\Init location in my SQL image.

Here is my docker-compose file in which I attempt to create a container with the image I created and map the appropriate ports and environment variables through. I also make sure to attach the database files that I need.

version: '3.4'

services:
  sqlserver:
    image: mynewsql:latest
    ports:
      - "14333:1433"
    environment:
      - sa_password=greatpassword
      - ACCEPT_EULA=Y
      - attach_dbs=[{"dbName":"MyDBNAME","dbFiles":["c:\\tmp\\MyDBNAME.mdf","c:\\tmp\\MyDBNAME_log.ldf"]}]

Running the docker-compose file and trying to connect to my database works.

The main problem though: the MyDBNAME database does not appear in SQL Server Management Studio.

One thing to note: I am able to successfully do this with a different mdf and ldf file in a previous version of the project but am not sure how or if I need to get the mdf and ldf files into a certain "state" in order for it to run properly in a container.

Any help would be greatly appreciated.

Upvotes: 2

Views: 2957

Answers (3)

g0rski
g0rski

Reputation: 49

For anyone who will have this issue, I sorted this out by:

  1. Exporting data tier application on Source database (dacpac)
  2. Starting container and exposing ports to connect from my local machine
  3. Connecting to container with SSMS (Sql Server Management Studio)
  4. Deploiong data tier application from step 2
  5. Finding mdf and ldf and using that as future starting point ;-)

Upvotes: 0

noblerare
noblerare

Reputation: 11933

For future reference, I figured out my own issue. The issue was that I had to change the containment type of the database that I wanted attached.

I opened Microsoft SQL Management Studio, right-clicked the database whose mdf and ldf files, I wanted and select Properties. Then, I select the Options page on the left-hand side and in the ContainmentType, I select None instead of Partial. After clicking OK, I copied the newly modified mdf and ldf files, attached those via the attach_dbs attribute and voila! The database attached successfully and appears in my Docker SQL instance.

Upvotes: 2

zigmund
zigmund

Reputation: 61

I suppose that your problem can be caused by invalid datatype. In your case -attach_dbs treated by YAML as an array meanwhile PowerShell script that attaches database expect it to be string 

[Parameter(Mandatory=$false)]
[string]$attach_dbs

Try to enclose the parameter in single quotes (') like this:

- attach_dbs='[{"dbName":"MyDBNAME","dbFiles":["c:\\tmp\\MyDBNAME.mdf","c:\\tmp\\MyDBNAME_log.ldf"]}]'

Upvotes: 0

Related Questions