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

Note: this article is published on 03/14/2023.

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

Container (1), WSL

Container (2), Docker
Container (3), Installing SQL Server into Docker
- Container (3-1), Get the Backup File into Container --- this article

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.

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

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

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

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

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: