CODEWITHSUNDEEP
sql-serverdocker
Naftis
Naftis

Reputation: 4559

Creating a docker image with SQL Server (Linux) and a database of my own: database not found in container

I'm starting with Docker and I need to create an image including SQL Server and my own database, to be later consumed in a multiple-containers stack.

My host is a Windows 10 Pro workstation, but I'm going to use Linux containers for all these layers. So, first of all I'm going to create an image derived from microsoft/mssql-server-linux:2017-latest, with my own database in it.

I'm following the steps listed at https://learn.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container, i.e.:

1.open an elevated Powershell prompt and execute:

docker pull microsoft/mssql-server-linux:2017-latest

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=mypwdhere" --name "sql1" -p 1401:1433 -v sql1data:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest

I can now ensure that the container is up with docker ps -a.

2.copy a backup file into the container:

docker cp mydatabase.bak sql1:/home

3.I can use this command to get the logical names of the files to be restored:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypwdhere" -Q "RESTORE FILELISTONLY FROM DISK='/home/mydatabase.bak'"

4.once I get these names, I run the restore command:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypwdhere" -Q "RESTORE DATABASE [mydatabase] FROM DISK='/home/mydatabase.bak' WITH MOVE 'mydatabase' TO '/var/opt/mssql/data/mydatabase.mdf', MOVE 'mydatabase_log' TO '/var/opt/mssql/data/mydatabase.ldf'"

The restore command is successful.

5.finally, I remove the bak file:

docker exec -it sql1 rm /home/mydatabase.bak

To check if all is OK, I can list the databases in the container with this command:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypwdhere" -Q "SELECT Name FROM sys.Databases"

When I execute this, I can see that my database is listed.

6.I create an image from the container:

docker commit sql1 mydatabase-sql

I can run this image in a container like:

docker run --name mydb-sql -p 1401:1433 mydatabase-sql

Now the server listens at 127.0.0.1 (localhost) port 1401. I can successfully login with SSMS with: localhost,1401; user=sa; password=mypwdhere.

The problem is that once connected I cannot see my database. I can only see the system databases like master, and nothing else. If I try to execute a query directly from the container, like this:

docker exec -it mydb-sql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypwdhere" -Q "USE [mydatabase]; SELECT COUNT(*) FROM Document"

I get an error telling me that this database does not exist. In fact, if I try listing the databases with this command:

docker exec -it mydb-sql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypwdhere" -Q "SELECT Name FROM sys.Databases"

I get only master, tempdb, model, msdb. What I'm missing here?

Upvotes: 0

Views: 1643

Answers (2)

paul stanton
paul stanton

Reputation: 964

Another option is Microsoft Windows Server core 1709 includes network SMB file share support, enabling remote mounts to databases. You would need to run a Dockerfile for each container build (given mount points don't persist in an image as noted above).

Upvotes: 0

Erik Dannenberg
Erik Dannenberg

Reputation: 6086

In step 1 you are mounting a volume in the container with:

... -v sql1data:/var/opt/mssql

As a consequence any db operations are not part of the container file system and hence not included when running docker commit later.

Having runtime data, like databases, in a separate Docker volume is best practice, but ommitting the mount should give you the expected result, though I would recommend to stop the container before commiting the final image.

Also, unless you are just playing around have a look at Dockerfile on how to properly build a Docker image. commit is not recommended due to lacking build repeatability.

Upvotes: 1

Related Questions