gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertDeze browser wordt niet meer ondersteund.
Upgrade naar Microsoft Edge om te profiteren van de nieuwste functies, beveiligingsupdates en technische ondersteuning.
Applies to:
SQL Server - Linux
This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2017 (14.x) Linux container image running on Docker.
This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2019 (15.x) Linux container image running on Docker.
This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2022 (16.x) Linux container image running on Docker.
This section provides deployment options for your environment.
sqlcmd doesn't currently support the MSSQL_PID
parameter when creating containers. If you use the sqlcmd instructions in this tutorial, you create a container with the Developer edition of SQL Server. Use the command line interface (CLI) instructions to create a container using the license of your choice. For more information, see Deploy and connect to SQL Server Linux containers.
Open a bash terminal on Linux.
Pull the SQL Server 2017 (14.x) Linux container image from the Microsoft Container Registry.
sudo docker pull mcr.microsoft.com/mssql/server:2017-latest
To run the container image with Docker, you can use the following command:
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
--name 'sql1' -p 1401:1433 \
-v sql1data:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2017-latest
Waarschuwing
Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
This command creates a SQL Server 2017 (14.x) container with the Developer edition (default). SQL Server port 1433
is exposed on the host as port 1401
. The optional -v sql1data:/var/opt/mssql
parameter creates a data volume container named sql1data
. This is used to persist the data created by SQL Server.
Belangrijk
This example uses a data volume container within Docker. For more information, see Configure SQL Server container images on Docker.
To view your containers, use the docker ps
command.
sudo docker ps -a
If the STATUS
column shows a status of Up
, then SQL Server is running in the container and listening on the port specified in the PORTS
column. If the STATUS
column for your SQL Server container shows Exited
, see Troubleshoot SQL Server Docker containers.
$ sudo docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
941e1bdf8e1d mcr.microsoft.com/mssql/server/mssql-server-linux "/bin/sh -c /opt/m..." About an hour ago Up About an hour 0.0.0.0:1401->1433/tcp sql1
Open a bash terminal on Linux.
Pull the SQL Server 2019 (15.x) Linux container image from the Microsoft Container Registry.
sudo docker pull mcr.microsoft.com/mssql/server:2019-latest
To run the container image with Docker, you can use the following command:
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
--name 'sql1' -p 1401:1433 \
-v sql1data:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2019-latest
Waarschuwing
Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
This command creates a SQL Server 2019 (15.x) container with the Developer edition (default). SQL Server port 1433
is exposed on the host as port 1401
. The optional -v sql1data:/var/opt/mssql
parameter creates a data volume container named sql1data
. This is used to persist the data created by SQL Server.
Belangrijk
This example uses a data volume container within Docker. For more information, see Configure SQL Server container images on Docker.
To view your containers, use the docker ps
command.
sudo docker ps -a
If the STATUS
column shows a status of Up
, then SQL Server is running in the container and listening on the port specified in the PORTS
column. If the STATUS
column for your SQL Server container shows Exited
, see Troubleshoot SQL Server Docker containers.
$ sudo docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
941e1bdf8e1d mcr.microsoft.com/mssql/server/mssql-server-linux "/bin/sh -c /opt/m..." About an hour ago Up About an hour 0.0.0.0:1401->1433/tcp sql1
Open a bash terminal on Linux.
Pull the SQL Server 2022 (16.x) Linux container image from the Microsoft Container Registry.
sudo docker pull mcr.microsoft.com/mssql/server:2022-latest
To run the container image with Docker, you can use the following command:
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
--name 'sql1' -p 1401:1433 \
-v sql1data:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2022-latest
Waarschuwing
Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
This command creates a SQL Server 2022 (16.x) container with the Developer edition (default). SQL Server port 1433
is exposed on the host as port 1401
. The optional -v sql1data:/var/opt/mssql
parameter creates a data volume container named sql1data
. This is used to persist the data created by SQL Server.
Belangrijk
This example uses a data volume container within Docker. For more information, see Configure SQL Server container images on Docker.
To view your containers, use the docker ps
command.
sudo docker ps -a
If the STATUS
column shows a status of Up
, then SQL Server is running in the container and listening on the port specified in the PORTS
column. If the STATUS
column for your SQL Server container shows Exited
, see Troubleshoot SQL Server Docker containers.
$ sudo docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
941e1bdf8e1d mcr.microsoft.com/mssql/server/mssql-server-linux "/bin/sh -c /opt/m..." About an hour ago Up About an hour 0.0.0.0:1401->1433/tcp sql1
The sa
account is a system administrator on the SQL Server instance that's created during setup. After you create your SQL Server container, the MSSQL_SA_PASSWORD
environment variable you specified is discoverable by running echo $MSSQL_SA_PASSWORD
in the container. For security purposes, change your sa
password:
Choose a strong password to use for the sa
account. Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
Use docker exec
to run the sqlcmd utility to change the password through a Transact-SQL statement. Replace <old-password>
and <new-password>
with your own password values:
Belangrijk
The SA_PASSWORD
environment variable is deprecated. Use MSSQL_SA_PASSWORD
instead.
sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
-S localhost -U sa -P '<old-password>' \
-Q 'ALTER LOGIN sa WITH PASSWORD="<new-password>"'
docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
-S localhost -U sa -P "<old-password>" `
-Q "ALTER LOGIN sa WITH PASSWORD='<new-password>'"
This tutorial uses the Wide World Importers sample databases for Microsoft SQL. Use the following steps to download and copy the Wide World Importers database backup file into your SQL Server container.
First, use docker exec
to create a backup folder. The following command creates a /var/opt/mssql/backup
directory inside the SQL Server container.
sudo docker exec -it sql1 mkdir /var/opt/mssql/backup
Next, download the WideWorldImporters-Full.bak file to your host machine. The following commands navigate to the home/user directory and downloads the backup file as wwi.bak
.
cd ~
curl -L -o wwi.bak 'https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak'
Use docker cp
to copy the backup file into the container in the /var/opt/mssql/backup
directory.
sudo docker cp wwi.bak sql1:/var/opt/mssql/backup
The backup file is now located inside the container. Before restoring the backup, it's important to know the logical file names and file types inside the backup. The following Transact-SQL commands inspect the backup and perform the restore using sqlcmd in the container.
Tip
This tutorial uses sqlcmd inside the container, because the container comes with this tool preinstalled. However, you can also run Transact-SQL statements with other client tools outside of the container, such as SQL Server extension for Visual Studio Code or Use SQL Server Management Studio on Windows to manage SQL Server on Linux. To connect, use the host port that was mapped to port 1433 in the container. In this example, the host and port are localhost,1401
on the host machine, and Host_IP_Address,1401
remotely.
Run sqlcmd inside the container to list out logical file names and paths inside the backup. This is done with the RESTORE FILELISTONLY
Transact-SQL statement.
sudo docker exec -it sql1 /opt/mssql-tools18/bin/sqlcmd -S localhost \
-U sa -P '<new-password>' \
-Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/wwi.bak"' \
| tr -s ' ' | cut -d ' ' -f 1-2
The results should look similar to the following output:
LogicalName PhysicalName
------------------------------------------
WWI_Primary D:\Data\WideWorldImporters.mdf
WWI_UserData D:\Data\WideWorldImporters_UserData.ndf
WWI_Log E:\Log\WideWorldImporters.ldf
WWI_InMemory_Data_1 D:\Data\WideWorldImporters_InMemory_Data_1
Call the RESTORE DATABASE
command to restore the database inside the container. Specify new paths for each of the files in the previous step.
sudo docker exec -it sql1 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q 'RESTORE DATABASE WideWorldImporters FROM DISK = "/var/opt/mssql/backup/wwi.bak" WITH MOVE "WWI_Primary" TO "/var/opt/mssql/data/WideWorldImporters.mdf", MOVE "WWI_UserData" TO "/var/opt/mssql/data/WideWorldImporters_userdata.ndf", MOVE "WWI_Log" TO "/var/opt/mssql/data/WideWorldImporters.ldf", MOVE "WWI_InMemory_Data_1" TO "/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1"'
The results should look similar to the following output:
Processed 1464 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 33 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
Processed 3862 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Converting database 'WideWorldImporters' from version 852 to the current version 869.
Database 'WideWorldImporters' running the upgrade step from version 852 to version 853.
Database 'WideWorldImporters' running the upgrade step from version 853 to version 854.
Database 'WideWorldImporters' running the upgrade step from version 854 to version 855.
Database 'WideWorldImporters' running the upgrade step from version 855 to version 856.
Database 'WideWorldImporters' running the upgrade step from version 856 to version 857.
Database 'WideWorldImporters' running the upgrade step from version 857 to version 858.
Database 'WideWorldImporters' running the upgrade step from version 858 to version 859.
Database 'WideWorldImporters' running the upgrade step from version 859 to version 860.
Database 'WideWorldImporters' running the upgrade step from version 860 to version 861.
Database 'WideWorldImporters' running the upgrade step from version 861 to version 862.
Database 'WideWorldImporters' running the upgrade step from version 862 to version 863.
Database 'WideWorldImporters' running the upgrade step from version 863 to version 864.
Database 'WideWorldImporters' running the upgrade step from version 864 to version 865.
Database 'WideWorldImporters' running the upgrade step from version 865 to version 866.
Database 'WideWorldImporters' running the upgrade step from version 866 to version 867.
Database 'WideWorldImporters' running the upgrade step from version 867 to version 868.
Database 'WideWorldImporters' running the upgrade step from version 868 to version 869.
RESTORE DATABASE successfully processed 58455 pages in 18.069 seconds (25.273 MB/sec).
Run the following query to display a list of database names in your container:
sudo docker exec -it sql1 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q 'SELECT name FROM sys.databases'
You should see WideWorldImporters
in the list of databases.
Follow these steps to make a change in the database.
Run a query to view the top 10 items in the Warehouse.StockItems
table.
sudo docker exec -it sql1 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q 'SELECT TOP 10 StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems ORDER BY StockItemID'
You should see a list of item identifiers and names:
StockItemID StockItemName
----------- -----------------
1 USB missile launcher (Green)
2 USB rocket launcher (Gray)
3 Office cube periscope (Black)
4 USB food flash drive - sushi roll
5 USB food flash drive - hamburger
6 USB food flash drive - hot dog
7 USB food flash drive - pizza slice
8 USB food flash drive - dim sum 10 drive variety pack
9 USB food flash drive - banana
10 USB food flash drive - chocolate bar
Update the description of the first item with the following UPDATE
statement:
sudo docker exec -it sql1 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q 'UPDATE WideWorldImporters.Warehouse.StockItems SET StockItemName="USB missile launcher (Dark Green)" WHERE StockItemID=1; SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'
You should see an output similar to the following text:
(1 rows affected)
StockItemID StockItemName
----------- ------------------------------------
1 USB missile launcher (Dark Green)
After you restore your database into a container, you might also want to regularly create database backups inside the running container. The steps follow a similar pattern to the previous steps but in reverse.
Use the BACKUP DATABASE
Transact-SQL command to create a database backup in the container. This tutorial creates a new backup file, wwi_2.bak
, in the previously created /var/opt/mssql/backup
directory.
sudo docker exec -it sql1 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q "BACKUP DATABASE [WideWorldImporters] TO DISK = N'/var/opt/mssql/backup/wwi_2.bak' WITH NOFORMAT, NOINIT, NAME = 'WideWorldImporters-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
The results should look similar to the following output:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
Processed 1200 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
80 percent processed.
Processed 3865 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Processed 938 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
100 percent processed.
BACKUP DATABASE successfully processed 59099 pages in 25.056 seconds (18.427 MB/sec).
Next, copy the backup file out of the container and onto your host machine.
cd ~
sudo docker cp sql1:/var/opt/mssql/backup/wwi_2.bak wwi_2.bak
ls -l wwi*
In addition to taking database backups for protecting your data, you can also use data volume containers. The beginning of this tutorial created the sql1
container with the -v sql1data:/var/opt/mssql
parameter. The sql1data
data volume container persists the /var/opt/mssql
data even after the container is removed. The following steps completely remove the sql1
container and then create a new container, sql2
, with the persisted data.
Stop the sql1
container.
sudo docker stop sql1
Remove the container. This doesn't delete the previously created sql1data
data volume container and the persisted data in it.
sudo docker rm sql1
Create a new container, sql2
, and reuse the sql1data
data volume container.
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
--name 'sql2' -e 'MSSQL_PID=Developer' -p 1401:1433 \
-v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest
The Wide World Importers database is now in the new container. Run a query to verify the previous change you made.
sudo docker exec -it sql2 /opt/mssql-tools/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q 'SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'
The sa
password isn't the password you specified for the sql2
container, MSSQL_SA_PASSWORD=<password>
. All of the SQL Server data was restored from sql1
, including the changed password from earlier in the tutorial. In effect, some options like this are ignored due to restoring the data in /var/opt/mssql. For this reason, the password is <new-password>
as shown here.
Stop the sql1
container.
sudo docker stop sql1
Remove the container. This doesn't delete the previously created sql1data
data volume container and the persisted data in it.
sudo docker rm sql1
Create a new container, sql2
, and reuse the sql1data
data volume container.
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
--name 'sql2' -e 'MSSQL_PID=Developer' -p 1401:1433 \
-v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest
The Wide World Importers database is now in the new container. Run a query to verify the previous change you made.
sudo docker exec -it sql2 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q 'SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'
The sa
password isn't the password you specified for the sql2
container, MSSQL_SA_PASSWORD=<password>
. All of the SQL Server data was restored from sql1
, including the changed password from earlier in the tutorial. In effect, some options like this are ignored due to restoring the data in /var/opt/mssql. For this reason, the password is <new-password>
as shown here.
Stop the sql1
container.
sudo docker stop sql1
Remove the container. This doesn't delete the previously created sql1data
data volume container and the persisted data in it.
sudo docker rm sql1
Create a new container, sql2
, and reuse the sql1data
data volume container.
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<password>' \
--name 'sql2' -e 'MSSQL_PID=Developer' -p 1401:1433 \
-v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2022-latest
The Wide World Importers database is now in the new container. Run a query to verify the previous change you made.
sudo docker exec -it sql2 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa -P '<new-password>' \
-Q 'SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'
The sa
password isn't the password you specified for the sql2
container, MSSQL_SA_PASSWORD=<password>
. All of the SQL Server data was restored from sql1
, including the changed password from earlier in the tutorial. In effect, some options like this are ignored due to restoring the data in /var/opt/mssql
. For this reason, the password is <new-password>
as shown here.
Waarschuwing
Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.
In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server 2017 (14.x) in a container. You learned how to:
In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server 2019 (15.x) in a container. You learned how to:
In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server 2022 (16.x) in a container. You learned how to:
Next, review other container configuration and troubleshooting scenarios:
Did you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.
For more information, see How to contribute to SQL Server documentation
gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertTraining
Certificering
Microsoft Certified: Azure Database Administrator Associate - Certifications
Beheer een SQL Server-databaseinfrastructuur voor cloud-, on-premises en hybride relationele databases met behulp van de relationele Microsoft PaaS-databaseaanbiedingen.
Documentatie
Sql Server Linux-containers beveiligen - SQL Server
Krijg inzicht in de verschillende manieren om LINUX-containers van SQL Server te beveiligen en hoe u containers kunt uitvoeren als verschillende niet-hoofdgebruikers op de host.