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

適用於: yesSQL Server (僅限 Linux)沒有Azure SQL Database沒有Azure SQL 資料倉儲沒有平行資料倉儲 APPLIES TO: yesSQL Server (Linux only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本教學課程會示範如何移動,並將 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 備份檔案還原至 SQL Server 2019 預覽 Linux 容器映像在 Docker 上執行。This tutorial demonstrates how to move and restore a SQL Server backup file into a SQL Server 2019 preview 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.

先決條件Prerequisites

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

  1. 開啟 bash 終端機,在 Linux/Mac 上的或在 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
    

    提示

    在本教學課程中,docker 命令範例可以同時用於 bash 殼層 (Linux/Mac) 和 PowerShell (Windows)。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
    

    此命令會建立 SQL Server 2017 容器與開發人員版本 (預設值)。This command creates a SQL Server 2017 container with the Developer edition (default). SQL Server 連接埠1433年在連接埠與主機上公開1401年SQL Server port 1433 is exposed on the host as port 1401. 選擇性-v sql1data:/var/opt/mssql參數會建立名為的資料磁碟區容器sql1ddataThe 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.

    注意

    在容器中執行生產 SQL Server 版本的程序會稍有不同。The process for running production SQL Server editions in containers is slightly different. 如需詳細資訊,請參閱執行生產容器映像For more information, see Run production container images. 如果您使用相同的容器名稱和連接埠,本逐步解說的其餘部分仍適用於實際執行的容器。If you use the same container names and ports, the rest of this walkthrough still works with production containers.

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

    sudo docker ps -a
    
    docker ps -a
    
  5. 如果狀態資料行會顯示狀態為向上、 然後 SQL Server 正在執行中容器和接聽的通訊埠中指定連接埠資料行。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 容器節目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. 開啟 bash 終端機,在 Linux/Mac 上的或在 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 preview Linux container image from Docker Hub.

    sudo docker pull mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu
    
    docker pull mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu
    

    提示

    在本教學課程中,docker 命令範例可以同時用於 bash 殼層 (Linux/Mac) 和 PowerShell (Windows)。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:vNext-CTP2.0-ubuntu
    
    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:vNext-CTP2.0-ubuntu
    

    此命令會建立 SQL Server 2019 預覽容器與開發人員版本 (預設值)。This command creates a SQL Server 2019 preview container with the Developer edition (default). SQL Server 連接埠1433年在連接埠與主機上公開1401年SQL Server port 1433 is exposed on the host as port 1401. 選擇性-v sql1data:/var/opt/mssql參數會建立名為的資料磁碟區容器sql1ddataThe 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. 如果狀態資料行會顯示狀態為向上、 然後 SQL Server 正在執行中容器和接聽的通訊埠中指定連接埠資料行。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 容器節目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 gets created during setup. 建立您的 SQL Server 容器之後,在容器中執行 echo $MSSQL_SA_PASSWORD,即可探索您指定的 MSSQL_SA_PASSWORD 環境變數。After creating 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 sqlcmd to change the password using Transact-SQL. <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. 下列命令會建立 /var/opt/mssql/backup目錄內的 SQL Server 容器。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. 下列命令瀏覽至首頁/使用者目錄,並下載做為備份的檔案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 Code或是SQL Server Management StudioHowever, 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,1401年遠端。In 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"

您應該會看到WideWorldImporters中的資料庫清單。You should see WideWorldImporters in the list of databases.

進行變更Make a change

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

  1. 執行查詢,以檢視中的前 10 個項目Warehouse.StockItems資料表。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 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. 本教學課程中建立新的備份檔案, wwi_2.bak,在先前建立 /var/opt/mssql/backup目錄。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-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 -l wwi*
    

使用必要的資料Use the persisted data

除了資料庫備份來保護您的資料,您也可以使用資料磁碟區容器。In addition to taking database backups for protecting your data, you can also use data volume containers. 本教學課程中建立的開頭sql1容器-v sql1data:/var/opt/mssql參數。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容器,然後再建立新的容器, sql2,與保存的資料。The 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:vNext-CTP2.0-ubuntu
    
    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:vNext-CTP2.0-ubuntu
    
  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 preview. 您已學到如何以:You learned how to:

  • 建立 SQL Server Linux 容器映像。Create SQL Server Linux container images.
  • SQL Server 資料庫備份複製到容器。Copy SQL Server database backups into a container.
  • 執行 TRANSACT-SQL 陳述式內的容器sqlcmdRun 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: