Attaching / restoring databases inside a container
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/aaronbertrand/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.
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 [email protected] 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,
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/aaronbertrand/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/StackOverflow2013_1.mdf'), (name = so2, filename = N'/var/opt/mssql/data/StackOverflow2013_2.ndf'), (name = so3, filename = N'/var/opt/mssql/data/StackOverflow2013_3.ndf'), (name = so4, filename = N'/var/opt/mssql/data/StackOverflow2013_4.ndf') LOG ON (name = solog, filename = N'/var/opt/mssql/data/StackOverflow2013_log.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
docker run --name MyContainer2 -v /Users/aaronbertrand/Downloads/whatever/:/whatever -p 2020:1433 -i -d -e ACCEPT_EULA=Y -e [email protected] 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:
Unable to open the physical file "/whatever/so1.mdf". Operating system error 87: "87(The parameter is incorrect.)".
Msg 1802, Level 16, State 7
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
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
[email protected] 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):