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).
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):
![Container (3-1), Get Backup File into Container]()
We get the screen: Locate Backup File:
![Container (3-1), Get Backup File into Container]()
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
![Container (3-1), Get Backup File into Container]()
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:
![Container (3-1), Get Backup File into Container]()
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
![Container (3-1), Get Backup File into Container]()
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:
![Container (3-1), Get Backup File into Container]()
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
![Container (3-1), Get Backup File into Container]()
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:
![Container (3-1), Get Backup File into Container]()
References: