在 Linux Docker 容器中還原 SQL Server 資料庫Restore a SQL Server database in a Linux Docker container

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) - Linux適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) - Linux

本教學課程說明如何將 SQL Server 備份檔案移動及還原至在 Docker 上執行的 SQL Server 2017 Linux 容器映像中。This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2017 Linux container image running on Docker.

本教學課程說明如何將 SQL Server 備份檔案移動及還原至執行於 Docker 上的 SQL Server 2019 Linux 容器映像中。This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2019 Linux container image running on Docker.

  • 提取並執行最新的 SQL Server Linux 容器映像。Pull and run the latest SQL Server Linux container image.
  • 將 Wide World Importers 資料庫檔案複製到容器中。Copy the Wide World Importers database file into the container.
  • 在容器中還原資料庫。Restore the database in the container.
  • 執行 Transact-SQL 陳述式以檢視及修改資料庫。Run Transact-SQL statements to view and modify the database.
  • 備份已修改的資料庫。Backup the modified database.

PrerequisitesPrerequisites

提取及執行容器映像Pull and run the container image

  1. 在 Linux/Mac 上開啟 Bash 終端機,或在 Windows 上開啟已提高權限的 PowerShell 工作階段。Open a bash terminal on Linux/Mac or an elevated PowerShell session on Windows.

  2. 從 Docker Hub 提取 SQL Server 2017 Linux 容器映像。Pull the SQL Server 2017 Linux container image from Docker Hub.

    sudo docker pull mcr.microsoft.com/mssql/server:2017-latest
    
    docker pull mcr.microsoft.com/mssql/server:2017-latest
    

    提示

    在這整個教學課程中,針對 Bash 殼層 (Linux/Mac) 和 PowerShell (Windows) 都有提供 Docker 命令範例。Throughout this tutorial, docker command examples are given for both the bash shell (Linux/Mac) and PowerShell (Windows).

  3. 若要使用 Docker 來執行容器映像,您可以使用下列命令:To run the container image with Docker, you can use the following command:

    sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \
       --name 'sql1' -p 1401:1433 \
       -v sql1data:/var/opt/mssql \
       -d mcr.microsoft.com/mssql/server:2017-latest
    
    docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" `
       --name "sql1" -p 1401:1433 `
       -v sql1data:/var/opt/mssql `
       -d mcr.microsoft.com/mssql/server:2017-latest
    

    此命令會建立具有 Developer 版本 (預設值) 的 SQL Server 2017 容器。This command creates a SQL Server 2017 container with the Developer edition (default). SQL Server 連接埠 1433 在主機上會公開為連接埠 1401SQL Server port 1433 is exposed on the host as port 1401. 選擇性的 -v sql1data:/var/opt/mssql 參數會建立名為 sql1ddata 的資料磁碟區容器。The optional -v sql1data:/var/opt/mssql parameter creates a data volume container named sql1ddata. 這會用來保存 SQL Server 所建立的資料。This is used to persist the data created by SQL Server.

    重要

    此範例使用 Docker 中的資料磁碟區容器。This example uses a data volume container within Docker. 請注意,如果您改為選擇對應主機目錄,此方法在 Mac 和 Windows 上的 Docker 有限制。If you instead chose to map a host directory, note that there are limitations for this approach on Docker for Mac and Windows. 如需詳細資訊,請參閱在 Docker 上設定 SQL Server 容器映像For more information, see Configure SQL Server container images on Docker.

  4. 若要檢視 Docker 容器,請使用 docker ps 命令。To view your Docker containers, use the docker ps command.

    sudo docker ps -a
    
    docker ps -a
    
  5. STATUS 欄位顯示的狀態含 Up,表示 SQL Server 正在容器中執行且接聽於 PORTS 欄位中指定的連接埠。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. 若 SQL Server 容器的 STATUS 欄位顯示 Exited,請參閱設定指南的<疑難排解>一節If the STATUS column for your SQL Server container shows Exited, see the Troubleshooting section of the configuration guide.

$ 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
  1. 在 Linux/Mac 上開啟 Bash 終端機,或在 Windows 上開啟已提高權限的 PowerShell 工作階段。Open a bash terminal on Linux/Mac or an elevated PowerShell session on Windows.

  2. 從 Docker Hub 提取 SQL Server 2019 Linux 容器映像。Pull the SQL Server 2019 Linux container image from Docker Hub.

    sudo docker pull mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04
    
    docker pull mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04
    

    提示

    在這整個教學課程中,針對 Bash 殼層 (Linux/Mac) 和 PowerShell (Windows) 都有提供 Docker 命令範例。Throughout this tutorial, docker command examples are given for both the bash shell (Linux/Mac) and PowerShell (Windows).

  3. 若要使用 Docker 來執行容器映像,您可以使用下列命令:To run the container image with Docker, you can use the following command:

    sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \
       --name 'sql1' -p 1401:1433 \
       -v sql1data:/var/opt/mssql \
       -d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04
    
    docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" `
       --name "sql1" -p 1401:1433 `
       -v sql1data:/var/opt/mssql `
       -d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04
    

    此命令會建立 Developer 版本 (預設值) 的 SQL Server 2019 容器。This command creates a SQL Server 2019 container with the Developer edition (default). SQL Server 連接埠 1433 在主機上會公開為連接埠 1401SQL Server port 1433 is exposed on the host as port 1401. 選擇性的 -v sql1data:/var/opt/mssql 參數會建立名為 sql1ddata 的資料磁碟區容器。The optional -v sql1data:/var/opt/mssql parameter creates a data volume container named sql1ddata. 這會用來保存 SQL Server 所建立的資料。This is used to persist the data created by SQL Server.

  4. 若要檢視 Docker 容器,請使用 docker ps 命令。To view your Docker containers, use the docker ps command.

    sudo docker ps -a
    
    docker ps -a
    
  5. STATUS 欄位顯示的狀態含 Up,表示 SQL Server 正在容器中執行且接聽於 PORTS 欄位中指定的連接埠。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. 若 SQL Server 容器的 STATUS 欄位顯示 Exited,請參閱設定指南的<疑難排解>一節If the STATUS column for your SQL Server container shows Exited, see the Troubleshooting section of the configuration guide.

    $ 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
    

變更 SA 密碼Change the SA password

SA 帳戶是在安裝期間建立的 SQL Server 執行個體系統管理員。The SA account is a system administrator on the SQL Server instance that's created during setup. 在您建立 SQL Server 容器之後,在容器中執行 echo $MSSQL_SA_PASSWORD,即可探索您指定的 MSSQL_SA_PASSWORD 環境變數。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. 基於安全性考量,請變更您的 SA 密碼:For security purposes, change your SA password:

  1. 選擇要為 SA 使用者使用的強式密碼。Choose a strong password to use for the SA user.

  2. 使用 docker exec 來執行 sqlcmd 公用程式,以透過 Transact-SQL 陳述式來變更密碼。Use docker exec to run the sqlcmd utility to change the password through a Transact-SQL statement. <YourStrong!Passw0rd><YourNewStrong!Passw0rd> 取代為您自己的密碼值:Replace <YourStrong!Passw0rd> and <YourNewStrong!Passw0rd> with your own password values:

    sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
       -S localhost -U SA -P '<YourStrong!Passw0rd>' \
       -Q 'ALTER LOGIN SA WITH PASSWORD="<YourNewStrong!Passw0rd>"'
    
    docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
       -S localhost -U SA -P "<YourStrong!Passw0rd>" `
       -Q "ALTER LOGIN SA WITH PASSWORD='<YourNewStrong!Passw0rd>'"
    

將備份檔案複製到容器中Copy a backup file into the container

本教學課程使用 Wide World Importers 範例資料庫This tutorial uses the Wide World Importers sample database. 使用下列步驟將 Wide World Importers 資料庫備份檔案下載並複製到您的 SQL Server 容器中。Use the following steps to download and copy the Wide World Importers database backup file into your SQL Server container.

  1. 首先,使用 docker exec 來建立備份資料夾。First, use docker exec to create a backup folder. 下列命令會在 SQL Server 容器內建立 /var/opt/mssql/backup 目錄。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
    
    docker exec -it sql1 mkdir /var/opt/mssql/backup
    
  2. 接著,將 WideWorldImporters-Full.bak 檔案下載到您的主機電腦。Next, download the WideWorldImporters-Full.bak file to your host machine. 下列命令會瀏覽至 home/user 目錄,並將備份檔案下載成 wwi.bakThe 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'
    
    curl -OutFile "wwi.bak" "https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak"
    
  3. 使用 docker cp 將備份檔案複製到 /var/opt/mssql/backup 目錄中的容器。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
    
    docker cp wwi.bak sql1:/var/opt/mssql/backup
    

還原資料庫Restore the database

備份檔案現在已位於容器內。The backup file is now located inside the container. 還原備份之前,請務必了解備份內的邏輯檔案名稱和檔案類型。Before restoring the backup, it is important to know the logical file names and file types inside the backup. 下列 Transact-SQL 命令會使用容器中的 sqlcmd 來檢查備份及執行還原。The following Transact-SQL commands inspect the backup and perform the restore using sqlcmd in the container.

提示

此教學課程會使用容器內的 sqlcmd,因為容器已預先安裝此工具。This tutorial uses sqlcmd inside the container, because the container comes with this tool pre-installed. 不過,您也可以將 Transact-SQL 陳述式與容器外的其他用戶端工具 (例如 Visual Studio CodeSQL Server Management Studio) 搭配執行。However, you can also run Transact-SQL statements with other client tools outside of the container, such as Visual Studio Code or SQL Server Management Studio. 若要連線,請使用已對應至容器中連接埠 1433 的主機連接埠。To connect, use the host port that was mapped to port 1433 in the container. 在此範例中,這是主機電腦上的 localhost,1401,以及遠端的 Host_IP_Address,1401In this example, that is localhost,1401 on the host machine and Host_IP_Address,1401 remotely.

  1. 執行容器內的 sqlcmd 以列出備份內的邏輯檔案名稱和路徑。Run sqlcmd inside the container to list out logical file names and paths inside the backup. 這會使用 RESTORE FILELISTONLY Transact-SQL 陳述式來完成。This is done with the RESTORE FILELISTONLY Transact-SQL statement.

    sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost \
       -U SA -P '<YourNewStrong!Passw0rd>' \
       -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/wwi.bak"' \
       | tr -s ' ' | cut -d ' ' -f 1-2
    
    docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost `
       -U SA -P "<YourNewStrong!Passw0rd>" `
       -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/wwi.bak'"
    

    您應該會看到類似以下的輸出:You should see output similar to the following:

    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
    
  2. 呼叫 RESTORE DATABASE 命令以在容器內還原資料庫。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-tools/bin/sqlcmd \
       -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \
       -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"'
    
    docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
       -S localhost -U SA -P "<YourNewStrong!Passw0rd>" `
       -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'"
    

    您應該會看到類似以下的輸出:You should see output similar to the following:

    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).
    

驗證還原的資料庫Verify the restored database

執行下列查詢以顯示您容器中的資料庫名稱清單:Run the following query to display a list of database names in your container:

sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \
   -Q 'SELECT Name FROM sys.Databases'
docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
   -S localhost -U SA -P "<YourNewStrong!Passw0rd>" `
   -Q "SELECT Name FROM sys.Databases"

您應該會在資料庫清單中看見 WideWorldImportersYou should see WideWorldImporters in the list of databases.

進行變更Make a change

下列步驟會在資料庫中進行變更。The following steps make a change in the database.

  1. 執行查詢以檢視 Warehouse.StockItems 資料表中排名前 10 名的項目。Run a query to view the top 10 items in the Warehouse.StockItems table.

    sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
       -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \
       -Q 'SELECT TOP 10 StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems ORDER BY StockItemID'
    
    docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
       -S localhost -U SA -P "<YourNewStrong!Passw0rd>" `
       -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
    
  2. 使用下列 UPDATE 陳述式來更新第一個項目的描述:Update the description of the first item with the following UPDATE statement:

    sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
       -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \
       -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'
    
    docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
       -S localhost -U SA -P "<YourNewStrong!Passw0rd>" `
       -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 output similar to the following text:

    (1 rows affected)
    StockItemID StockItemName
    ----------- ------------------------------------
              1 USB missile launcher (Dark Green)
    

建立新的備份Create a new backup

將資料庫還原到容器中之後,您可能也會想要在執行中的容器內定期建立資料庫備份。After you've restored 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.

  1. 使用 BACKUP DATABASE Transact-SQL 命令在容器中建立資料庫備份。Use the BACKUP DATABASE Transact-SQL command to create a database backup in the container. 本教學課程會在先前建立的 /var/opt/mssql/backup 目錄中建立新的備份檔案 wwi_2.bakThis 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-tools/bin/sqlcmd \
       -S localhost -U SA -P '<YourNewStrong!Passw0rd>' \
       -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"
    
    docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
       -S localhost -U SA -P "<YourNewStrong!Passw0rd>" `
       -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"
    

    您應該會看到類似以下的輸出:You should see output similar to the following:

    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).
    
  2. 接著,將備份檔案從容器中複製到主機電腦上。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*
    
    cd ~
    docker cp sql1:/var/opt/mssql/backup/wwi_2.bak wwi_2.bak
    ls wwi*
    

使用保存的資料Use the persisted data

除了使用資料庫備份來保護您的資料之外,您也可以使用資料磁碟區容器。In addition to taking database backups for protecting your data, you can also use data volume containers. 本教學課程一開始已使用 -v sql1data:/var/opt/mssql 參數建立 sql1 容器。The beginning of this tutorial created the sql1 container with the -v sql1data:/var/opt/mssql parameter. sql1data 資料磁碟區容器即使在該容器被移除後,也會保存 /var/opt/mssql 資料。The sql1data data volume container persists the /var/opt/mssql data even after the container is removed. 下列步驟會將 sql1 容器完全移除,然後使用保存的資料來建立新容器 sql2The following steps completely remove the sql1 container and then create a new container, sql2, with the persisted data.

  1. 停止 sql1 容器。Stop the sql1 container.

    sudo docker stop sql1
    
    docker stop sql1
    
  2. 移除容器。Remove the container. 這不會刪除先前建立的 sql1data 資料磁碟區容器及其當中保存的資料。This does not delete the previously created sql1data data volume container and the persisted data in it.

    sudo docker rm sql1
    
    docker rm sql1
    
  3. 建立新容器 sql2,然後重複使用 sql1data 資料磁碟區容器。Create a new container, sql2, and reuse the sql1data data volume container.

    sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \
       --name 'sql2' -e 'MSSQL_PID=Developer' -p 1401:1433 \
       -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest
    
    docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" `
       --name "sql2" -e "MSSQL_PID=Developer" -p 1401:1433 `
       -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest
    
  4. Wide World Importers 資料庫現在已位於新的容器中。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 '<YourNewStrong!Passw0rd>' \
       -Q 'SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'
    
    docker exec -it sql2 /opt/mssql-tools/bin/sqlcmd `
       -S localhost -U SA -P "<YourNewStrong!Passw0rd>" `
       -Q "SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1"
    

    注意

    SA 密碼不是您為 sql2 容器指定的密碼 MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>The SA password is not the password you specified for the sql2 container, MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>. 所有 SQL Server 資料都已從 sql1 還原,包括本教學課程稍早變更的密碼。All of the SQL Server data was restored from sql1, including the changed password from earlier in the tutorial. 實際上,由於還原 /var/opt/mssql 中的資料,因此會忽略一些類似這樣的選項。In effect, some options like this are ignored due to restoring the data in /var/opt/mssql. 因此,如這裡所示,密碼是 <YourNewStrong!Passw0rd>For this reason, the password is <YourNewStrong!Passw0rd> as shown here.

  1. 停止 sql1 容器。Stop the sql1 container.

    sudo docker stop sql1
    
    docker stop sql1
    
  2. 移除容器。Remove the container. 這不會刪除先前建立的 sql1data 資料磁碟區容器及其當中保存的資料。This does not delete the previously created sql1data data volume container and the persisted data in it.

    sudo docker rm sql1
    
    docker rm sql1
    
  3. 建立新容器 sql2,然後重複使用 sql1data 資料磁碟區容器。Create a new container, sql2, and reuse the sql1data data volume container.

    sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \
       --name 'sql2' -e 'MSSQL_PID=Developer' -p 1401:1433 \
       -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04
    
    docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" `
       --name "sql2" -e "MSSQL_PID=Developer" -p 1401:1433 `
       -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04
    
  4. Wide World Importers 資料庫現在已位於新的容器中。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 '<YourNewStrong!Passw0rd>' \
       -Q 'SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1'
    
    docker exec -it sql2 /opt/mssql-tools/bin/sqlcmd `
       -S localhost -U SA -P "<YourNewStrong!Passw0rd>" `
       -Q "SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID=1"
    

    注意

    SA 密碼不是您為 sql2 容器指定的密碼 MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>The SA password is not the password you specified for the sql2 container, MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>. 所有 SQL Server 資料都已從 sql1 還原,包括本教學課程稍早變更的密碼。All of the SQL Server data was restored from sql1, including the changed password from earlier in the tutorial. 實際上,由於還原 /var/opt/mssql 中的資料,因此會忽略一些類似這樣的選項。In effect, some options like this are ignored due to restoring the data in /var/opt/mssql. 因此,如這裡所示,密碼是 <YourNewStrong!Passw0rd>For this reason, the password is <YourNewStrong!Passw0rd> as shown here.

後續步驟Next steps

在本教學課程中,您已了解如何在 Windows 上備份資料庫,然後將其移至執行 SQL Server 2017 的 Linux 伺服器。In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server 2017. 您已了解如何︰You learned how to:

在本教學課程中,您已了解如何在 Windows 上備份資料庫,然後將其移至執行 SQL Server 2019 的 Linux 伺服器。In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server 2019. 您已了解如何︰You learned how to:

  • 建立 SQL Server Linux 容器映像。Create SQL Server Linux container images.
  • 將 SQL Server 資料庫備份複製到容器中。Copy SQL Server database backups into a container.
  • 使用 sqlcmd在容器內執行 Transact-SQL 陳述式。Run Transact-SQL statements inside the container with sqlcmd.
  • 從容器建立並擷取備份檔案。Create and extract backup files from a container.
  • 使用 Docker 中的資料磁碟區容器來保存 SQL Server 資料。Use data volume containers in Docker to persist SQL Server data.

接下來,請檢閱其他 Docker 設定和疑難排解案例:Next, review other Docker configuration and troubleshooting scenarios: