I wanted to write a blog post which involved using the Microsoft AventureWorks sample database, but then I realised I didn’t know how to transfer the MDF file to Docker or even if I could attach the file using SQL Server on Linux. So instead this post will be on how to attach a SQL Server database in a Linux Docker container.
Let’s start up our Docker Container
If you haven’t already setup a SQL Server container using Docker you can refer to my post Installing SQL Server 2017 in a Linux container using Docker on Windows 10.
When managing containers, you can use the container id or the container name. Docker supplies containers with a randomly-generated name from two words joined by an underscore – I just wanted to emphasise this given my container name of nifty_golick used in the examples.
To get your container name, run the following from a command prompt to get a list of containers (need to use the -a switch to show all as by default shows just running).
docker ps -a
Now we have our name we can start the container. I like to use the interactive switch -I to attach the container’s STDIN. This way you get to see messages that you usually wouldn’t, for example, I have 164 days left before my evaluation period ends.
docker start nifty_golick -i
Transferring the MDF file to the Docker Container
Using the CP command you can transfer the MDF file between your local filesystem and the container.
The command below copies the MDF from my local D:\ drive to the default data directory of the SQL Server instance – /var/opt/mssql/data
docker cp d:\AdventureWorksDW2012_Data.mdf nifty_golick:/var/opt/mssql/data/AdventureWorksDW2012_Data.mdf
Open a Bash shell in a Docker Container without SSH
We can run a Bash shell in our running container. Using the following command, you can start to explore the filesystem.
docker exec -it nifty_golick bash
Ready to attach a SQL Server database in a Linux Docker container
Now we are ready to attach the database using the TSQL below. For this demo, I used Management Studio from my Laptop, to connect to SQL Server.
In the TSQL we need to use the FOR ATTACH_REBUILD_LOG argument as we have no log file to attach. It will create a 1MB log file in the default log file directory.
USE [master] GO CREATE DATABASE [AdventureWorksDW2012] ON ( FILENAME = N'/var/opt/mssql/data/AdventureWorksDW2012_Data.mdf' ) FOR ATTACH_REBUILD_LOG GO
NB If like me you sometimes script out TSQL using the script action inside Management Studio, be warned. Management Studio will use the backslash as the directory separator which when run will produce this error “A file activation error occurred.”