I'll get this out of the way first: I'm a Mac guy. If you're not using a Mac, probably the end of this post is the only thing of interest.

I know a lot of us are about to face extended periods of time at home, perhaps without the luxury of our powerful desktop machines — and their proximity to even more powerful SQL Server instances — at the office.

I have been using Docker containers for local development and testing for some time now; I first blogged about my steps into this brave new world back in late 2016. Most of the time, I just need to create some throwaway database, with a throwaway table, to prove a point or to validate an answer I've supplied.

Sometimes, though, I need to work with a real database. It's a little trickier to do this in a container, because it's isolated — I can't just attach or restore from my Downloads folder. I could fire up a VM and attach there, but I actually don't even use Parallels on my work laptop, and I find that using VMs leads to response times that are a lot more sluggish across the board.

It's not too difficult to get files into your containers, provided your instance will have a total size less than the size of the container, and this quick tutorial proved to be very helpful.

Have a database to attach / restore

For this example, I've downloaded the 50GB version of the Stack Overflow database; Brent Ozar has graciously provided these and also explains how to do download in How to Download the Stack Overflow Database. For me, I used curl to download the file, with the following syntax (-L means follow if the file has moved; -o shows progress):

cd /Users/me/Downloads
curl -L -o "so.7z" "http://downloads.brentozar.com.s3.amazonaws.com/StackOverflow2013_201809117.7z"

To extract the files, you may need 7-Zip, but on the Mac this is supported natively. This put 1 mdf file, 3 ndf files, 1 ldf file, and a readme file into a folder named StackOverflow2013_201809117. I renamed the folder so for simplicity, and shortened the file names.

Get a container up and running

I don't always name my containers, but when I do, I use really descriptive names.

docker run --name MyContainer1 -p 2019:1433 -i -d 
  -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=s@fe 
  mcr.microsoft.com/mssql/server:2019-latest

Giving the container a name helps my current scenario, because I can use it to easily copy files from the host to the container without having to look up the hex identifier it was assigned.

If you just have small files to attach or restore, you can get them into the container in a couple of quick steps.

Copy the files into the container

First, create a folder. (You don't need to do this if you're copying in .mdf files that should live in a folder that already exists, say, /var/opt/mssql/data.)

docker exec -it MyContainer1 mkdir "/var/opt/mssql/backup"

Then copy the files there (docker cp doesn't support wildcards, but you can use . to copy all files from the source folder):

cd /Users/me/Downloads/so
docker cp . MyContainer1:/var/opt/mssql/data/

Attach / restore the database

Once the files are there, you can connect through Azure Data Studio and attach the database:

CREATE DATABASE StackOverflow 
ON 
  (name = so1,   filename = N'/var/opt/mssql/data/SO2013_1.mdf'),
  (name = so2,   filename = N'/var/opt/mssql/data/SO2013_2.mdf'),
  (name = so3,   filename = N'/var/opt/mssql/data/SO2013_3.mdf'),
  (name = so4,   filename = N'/var/opt/mssql/data/SO2013_4.mdf')
LOG ON
  (name = solog, filename = N'/var/opt/mssql/data/SO2013.ldf')
FOR ATTACH;

It is a coincidence that the 50GB version of the Stack Overflow database just barely fits in a default container. I'd be in trouble as soon as I did anything to grow any of those files, or create / attach / grow any other database.

What if I have large files?

If you need to create, restore, or attach databases that are (or will be) larger than about 40 GB, you will need to mess with the container storage options at a higher level. I'll save that for another post. But if it's just the backup file that's too large for the container, or the backup file plus the restored copy are too big, you can add a mounted volume using -v:

docker run --name MyContainer2 -v /Users/me/Downloads/whatever/:/whatever 
  -p 2020:1433 -i -d -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=s@fe 
  mcr.microsoft.com/mssql/server:2019-latest

You may get prompted by one of these:

Once you're up and running, you can connect through ADS, and restore from that source:

RESTORE DATABASE whatever 
  FROM DISK = N'/whatever/whatever.bak'
  WITH MOVE whatever_data TO N'/var/opt/mssql/data/whatever.mdf', 
       MOVE ...

You can't attach files from there (at least not in a default configuration), since the container would need to be able to write to that location. The error message you get in that case isn't crystal clear:

Conclusion

I hope that this helps anyone struggling to get a real database into a container without having to manually script out the schema and data, which is a really tedious workaround. But this is all just for local development — for more serious use cases, like persisting data, see Anthony Nocentino, who has probably forgotten more about containers than I'll ever know:

• Persisting SQL Server Data in Docker Containers – Part 1 | Part 2 | Part 3
• Upgrading SQL Server 2017 Containers to 2019 non-root Containers with Data Volumes | Another Method

As an aside, don't use s@fe as your password. Your container will appear to start fine, but it will terminate shortly thereafter with the following error (and if you use -d, you won't see it):

Also, avoid special characters like $ in your password, because if you try to enter those manually during authentication – or configure something else to do so – they need to be escaped. Otherwise, you'll drive yourself batty trying to troubleshoot. Ask me how I know.

For local stuff, P@ssW0rd is probably fine (and I stick with a single password – not that one – for all my local stuff just to keep from confusing myself). But P@$$W0rd is just asking for trouble.