Container (3-1), Get Backup File Into Container

This series of articles is about Docker or Container, so I cannot find a suitable category. I designate it to DevOps because the Container is used for CI/CD (Continue Integration and Continue Deployment).

• Container (1), WSL 
  • Container (1-1), Windows Terminal
• Container (2), Docker 
• Container (3), Installing SQL Server into Docker 
  • Container (3-1), Get the Backup File into Container --- this article
  • Container (3-2), Manage Data in Docker Container

A - Introduction

After we install the SQL Server database into a container, we usually need to import a database into the SQL Server. However, the Container is running under a Linux system, moving data from Windows System into a Linux system is not automatic and even not an easy task. This article will discuss this issue. Contents of this article:

• A - Introduction
• B - What is the Problem
• C - Where is the Problem
• D - How to Solve the Problem
• E - Implementation

B - What is the Problem

After we install the SQL Server database into a container, we can open the database through SSMS. At this point, we need to get a database either from backup/restore or export/import. Now assume we have a backup database and need to restore into the SQL Server in Container:

Open SSMS, Choose database => Restore Database.

Choose Device => add (backup media):

We get the screen: Locate Backup File:

However, the opened file system from SQL Server in Docker Container is a Linux system, not Windows File System.

C - Where is the Problem

This article, MS SQL Server Docker data location - Stack Overflow, give the location 

The location for backup files are stored in Linux File System at

/var/opt/msswl/data

C#

Copy

This is exactly what we got from the screenshot above.

Note, we can get similar info from this article: Configure SQL Server settings on Linux - SQL Server | Microsoft Learn

D - How to Solve the Problem

This article, Restore a SQL Server database in a Linux container - SQL Server | Microsoft Learn. give the solution:

E - Implementation

1, Use the docker exec command to create a new directory in a Linux System:

docker exec
     -it sql_2022_1434
     mkdir /var/opt/mssql/backup

C#

Copy

where

• docker exec command runs a new command in a running container.
  • -it sql_2022_1434 --- Container Name
  • mkdir --- Linux command to create a new directory

Note:

docker exec is a powerful tool that gets inside our containers and debugs by running various commands. Coupled with the -it flags, we can get an interactive shell to perform deeper debugging.

Example: Open a Linux shell:

2, Use docker cp to copy the backup file into the Container in the /var/opt/mssql/backup directory.

docker cp enVision_Dev_08032023.bak sql_2022_1434:/var/opt/mssql/backup

C#

Copy

where

• docker cp command runs a new command in a running container.
  • enVision_Dev_08032023.bak --- backup file in the current folder of the host system (Windows)
  • sql_2022_1434 --- Container name
  • /var/opt/mssql/backup --- file path in the Linux System

Now, redo the Restore for the SQL Server in the Container sql_2022_1434. You will see the backup database file is in the Linux directory /var/opt/mssql/backup:

References:

• Restore a SQL Server database in a Linux container - SQL Server | Microsoft Learn
• Inspecting containers with the docker exec command | Docker High Performance - Second Edition (packtpub.com)
• How to Use a Docker Host Folder for a SQL Server Database - The SERO Group
• Step by step guide to run SQL Server in a Windows Docker container – Data Analytics Junkie (nexxtjump.com)
• MS SQL Server Docker data location - Stack Overflow
• Configure and customize SQL Server Docker containers - SQL Server | Microsoft Learn --- Persistent Data
• Container Storage Overview | Microsoft Learn
• Persistent Storage in Containers | Microsoft Learn