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, /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/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 -v:

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:

Msg 5120, Level 16, State 101
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.

 

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:

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):

ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am an architect at Wayfair.

1 Response

  1. March 16th, 2020

    […] Aaron Bertrand has a quick-and-easy method of restoring databases in a Docker container: […]

Leave a Reply