How to Use a Docker Host Folder for a SQL Server Database

In Getting Started with SQL Server in a Docker Container, I shared my SQL Server Docker container configuration. I described how to copy database backup files from your host to the container and then restore them in the container. That’s my usual approach. But what if you wanted the database to reside on the host instead of inside the Docker container? That would allow you to upgrade the container anytime you’d like and just run a few scripts to create users, attach databases, etc., afterward. That could be handy. So, let’s look at how to mount a Docker host folder in a containe for a SQL Server database.

Using a Docker host folder for a SQL Server database

For this example, I’m assuming you already have Docker Desktop installed and a SQL Server image available. If not, go back to the Getting Started blog before continuing. As before, I’m using a Windows host computer and SQL Server 2019 in a Ubuntu Linux image. A similar approach will work for other hosts and images.

Mounting a host folder to a Docker container

To use a host folder from inside the Docker container, we must mount the folder when we first run the container. We do this by adding the -v parameter to the same docker run command we used before.

docker run -v C:\Users\JoeWebb\sql:/mnt/myhost/sql  -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=34r0TNhvgOde" -p 1433:1433 --name sql2019a -h sql2019 -d mcr.microsoft.com/mssql/server:2019-latest

The -v parameter mounts the host volume to a folder in the container. In this example, I’m mounting the C:\Users\JoeWebb\sql folder to /mnt/myhost/sql in the container. If the mount location in the container doesn’t already exist, it will be created for you.

To briefly recap the other parameters, the ACCEPT_EULA parameter is where you agree to Microsoft’s End User Licensing Agreement, EULA. You set the sa password for the SQL instance using the SA_PASSWORD parameter. Map TCP ports using the -p parameter in the format <host port>:<container port>. Set the name for your container using the –name parameter and set your container’s hostname using the -h parameter. The -d parameter the image file to use.

Use the following command to verify that the container now has access to the mounted volume. The ls command list the contents of the mounted folder.

docker exec -it sql2019a ls -al /mnt/myhost/sql

The container can see host folder and lists its two subfolders, backup and data.

Attaching a database in the mounted host folder

Now that the host folder is available in your container, let’s attach the AdventureWorks2014 database to the sql2019a SQL Server instance. Open your favorite SQL Server query editor – Azure Data Studio, SQL Server Management Studio, Visual Studio Code, etc – and connect to the sql2019a instance. Run the following T-SQL command to attach the database. Notice that the data and log files are in the /mnt/myhost/sql/data folder.

CREATE DATABASE AdventureWorks2014 
ON
( NAME = AdventureWorks2014,
    FILENAME = '/mnt/myhost/sql/data/AdventureWorks2014_Data.mdf'), 
    (FILENAME = '/mnt/myhost/sql/data/AdventureWorks2014_Log.ldf')
    FOR ATTACH ;

Now you have the AdventureWorks2014 database available in the sql2019a instance, yet the data and log files actually live outside the container in a Docker host folder.

Restoring a database in the mounted folder.

You can also restore a database to the mounted folder. However with the current images and Docker versions, there seems to be a slight issue doing this directly. When you try to restore a database to a mounted folder, you may receive an error like:

Msg 3634, Level 16, State 1, Line 2
The operating system returned the error ‘2(The system cannot find the file specified.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘/mnt/myhost/sql/data/Baseball_Data.mdf’.

See sql server – Restore database in docker container – Stack Overflow for more information.

The work around for this is to first create an empty database on the mounted folder. Then restore the database from the backup file using WITH REPLACE.

Here a short script to restore the BaseballData database.

CREATE DATABASE BaseballData 
ON
( NAME = BaseBallData_Data,
    FILENAME = '/mnt/myhost/sql/data/BaseBallData_data.mdf',
    SIZE = 10,
    MAXSIZE = 100,
    FILEGROWTH = 5 )
LOG ON
( NAME = BaseBallData_log,
    FILENAME = '/mnt/myhost/sql/data/BaseBallData_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB ) ;


GO

RESTORE DATABASE BaseBallData 
    FROM DISK='/mnt/myhost/sql/backup/BaseBallData.bak' 
    WITH REPLACE,
    MOVE 'LahmansBaseballDB' TO '/mnt/myhost/sql/data/BaseballData_data.mdf', 
    MOVE 'LahmansBaseballDB_log' TO '/mnt/myhost/sql/data/BaseballData_log.ldf';

Note: when you use the -v parameter to mount a folder in the container, Docker Desktop may warn you that performance could be an issue. You’ll certainly want to test to see if that’s the case for you.

Want to work with The SERO Group?

We bring reliability, performance, and security to companies that traditionally could not justify an experienced DBA on staff. It’s called SEROShield. Want to learn more? It’s easy and there is no obligation. 

Schedule a call with us to get started.