快速入門:SQL 備份及還原至 Azure Blob 儲存體服務Quickstart: SQL backup and restore to Azure Blob storage service

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本快速入門可協助您了解如何將備份寫入至 Azure Blob 儲存體服務以及從中還原。This quickstart helps you understand how to write backups to and restore from the Azure Blob Storage Service. 此文章說明如何建立 Azure Blob 容器、將備份寫入到 Blob 服務,然後執行還原。The article explains how to create an Azure Blob Container, write a backup to the blob service, and then perform a restore.

PrerequisitesPrerequisites

若要完成本快速入門,您必須熟悉 SQL ServerSQL Server 備份與還原概念以及 T-SQL 語法。To complete this quickstart, you must be familiar with SQL ServerSQL Server backup and restore concepts and T-SQL syntax. 您需要 Azure 儲存體帳戶、SQL Server Management Studio (SSMS),以及對執行 SQL Server 伺服器之伺服器或 Azure SQL Database 受控執行個體的存取權。You need an Azure storage account, SQL Server Management Studio (SSMS), and access to either a server that's running SQL Server or an Azure SQL Database managed instance. 此外,用來發出 BACKUP 或 RESTORE 命令的帳戶,應該位於擁有 ALTER ANY CREDENTIAL 權限的 db_backupoperator 資料庫角色中。Additionally, the account used to issue the BACKUP and RESTORE commands should be in the db_backupoperator database role with alter any credential permissions.

建立 Azure Blob 容器Create Azure Blob container

容器會提供一組 Blob 的群組。A container provides a grouping of a set of blobs. 所有 Blob 都必須位於容器中。All blobs must be in a container. 儲存體帳戶可以包含不限數目的容器,但是至少必須具有一個容器。A storage account can contain an unlimited number of containers, but must have at least one container. 容器可以儲存不限數目的 Blob。A container can store an unlimited number of blobs.

若要建立容器,請遵循下列步驟:To create a Container, follow these steps:

  1. 開啟 Azure 入口網站。Open the Azure portal.

  2. 巡覽至您的儲存體帳戶。Navigate to your Storage Account.

  3. 選取儲存體帳戶,向下捲動至 [Blob 服務] 。Select the storage account, scroll down to Blob Services.

  4. 選取 [Blob] ,然後選取 [+ 容器] 以新增新的容器。Select Blobs and then select + Container to add a new container.

  5. 輸入容器名稱,並記下您指定的容器名稱。Enter the name for the container and make note of the container name you specified. 此資訊會在本快速入門稍後的 T-SQL 陳述式內的 URL (備份檔路徑) 中用到。This information is used in the URL (path to backup file) in the T-SQL statements later in this quickstart.

  6. 選取 [確定] 。Select OK.

    新增容器

注意

即使您選擇建立公用容器,SQL Server 備份與還原仍然需要儲存體帳戶的驗證。Authentication to the storage account is required for SQL Server backup and restore even if you choose to create a public container. 您也可以使用 REST API,以程式設計方式建立容器。You can also create a container programmatically using REST APIs. 如需詳細資訊,請參閱建立容器For more information, see Create container

建立測試資料庫Create a test database

在此步驟中,使用 SQL Server Management Studio (SSMS) 來建立測試資料庫。In this step, create a test database using SQL Server Management Studio (SSMS).

  1. 啟動 SQL Server Management Studio (SSMS) 並連線至 SQL Server 執行個體。Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
  2. 開啟 [新增查詢] 視窗。Open a New Query window.
  3. 執行下列 Transact-SQL (T-SQL) 程式碼以建立測試資料庫。Run the following Transact-SQL (T-SQL) code to create your test database. 重新整理 [物件總管] 中的 [資料庫] 節點以查看新的資料庫。Refresh the Databases node in Object Explorer to see your new database. 在 Azure SQL Database 受控執行個體上建立的新資料庫會自動啟用 TDE,因此您必須將它停用以繼續。Newly created databases on an Azure SQL Database managed instance automatically have TDE enabled so you'll need to disable it to proceed.
USE [master]
GO

-- Create database
CREATE DATABASE [SQLTestDB]
GO

-- Create table in database
USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR(100) NOT NULL,
    dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

-- Populate table 
USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

-- Disable TDE for newly-created databases on a managed instance 
USE [SQLTestDB];
GO
ALTER DATABASE [SQLTestDB] SET ENCRYPTION OFF;
GO

建立認證Create credential

依照下面的步驟使用 SQL Server Management Studio 中的 GUI 來建立認證。Use the GUI in SQL Server Management Studio to create the credential by following the steps below. 或者,您也能以程式設計方式建立認證。Alternatively, you can create the credential programmatically as well.

  1. 展開 SQL Server Management Studio(SSMS) 之 [物件總管] 內的 [資料庫] 節點。Expand the Databases node within Object Explorer of SQL Server Management Studio(SSMS).

  2. 以滑鼠右鍵按一下新的 SQLTestDB 資料庫,將滑鼠游標移到 [工作] 上方並選取 [備份] 以啟動 [備份資料庫] 精靈。Right-click your new SQLTestDB database, hover over Tasks and then select Back up... to launch the Back Up Database wizard.

  3. 從 [備份至] 目的地下拉式清單選取 [URL] ,然後選取 [新增] 以啟動 [選取備份目的地] 對話方塊。Select URL from the Back up to destination drop-down, and then select Add to launch the Select Backup Destination dialog box.

    備份至 URL

  4. 選取 [選取備份目的地] 對話方塊上的 [新增容器] 以啟動 [連線至 Microsoft 訂用帳戶] 視窗。Select New container on the Select Backup Destination dialog box to launch the Connect to a Microsoft Subscription window.

    備份目的地

  5. 選取 [登入] 以登入 Azure 入口網站,然後繼續完成登入程序。Sign in to the Azure portal by selecting Sign In... and then proceed through the sign-in process.

  6. 從下拉式清單選取您的訂用帳戶Select your subscription from the drop-drown.

  7. 從下拉式清單中選取您的儲存體帳戶Select your storage account from the drop-down.

  8. 從下拉式清單選取您先前建立的容器。Select the container you created previously from the drop-down.

  9. 選取 [建立認證] 以產生您的共用存取簽章 (SAS)Select Create Credential to generate your Shared Access Signature (SAS). 儲存此值,因為您將需要它才能進行還原。Save this value as you'll need it for the restore.

    建立認證

  10. 選取 [確定] 以關閉 [連線至 Microsoft 訂用帳戶] 視窗。Select OK to close the Connect to a Microsoft Subscription window. 這會在 [選取備份目的地] 對話方塊上填入 Azure 儲存體容器值。This populates the Azure storage container value on the Select Backup Destination dialog box. 選取 [確定] 以選擇選取的儲存體容器,並關閉對話方塊。Select OK to choose the selected storage container, and close the dialog box.

  11. 目前,您可以跳到下一節中的步驟 4 以快速建立資料庫備份,或關閉 [備份資料庫] 精靈以改為使用 Transact-SQL 備份資料庫來繼續。At this point, you can either skip ahead to step 4 in the next section to take the backup of the database, or close the Back up Database wizard if you want to proceed with using Transact-SQL to back up the database instead.

備份資料庫Back up database

在此步驟中,使用 SQL Server Management Studio 內的 GUI 或 Transact-SQL (T-SQL) 將資料庫 SQLTestDB 備份到您的 Azure Blob 儲存體帳戶。In this step, back up the database SQLTestDB to your Azure Blob storage account using either the GUI within SQL Server Management Studio, or Transact-SQL (T-SQL).

  1. 若 [備份資料庫] 精靈尚未開啟,請展開 SQL Server Management Studio(SSMS) 之 [物件總管] 內的 [資料庫] 節點。If the Back Up Database wizard is not already open, expand the Databases node within Object Explorer of SQL Server Management Studio(SSMS).

  2. 以滑鼠右鍵按一下新的 SQLTestDB 資料庫,將滑鼠游標移到 [工作] 上方並選取 [備份] 以啟動 [備份資料庫] 精靈。Right-click your new SQLTestDB database, hover over Tasks and then select Back up... to launch the Back Up Database wizard.

  3. 從 [備份至] 下拉式清單選取 [URL] ,然後選取 [新增] 以啟動 [選取備份目的地] 對話方塊。Select URL from the Back up to drop down, and then select Add to launch the Select Backup Destination dialog box.

    備份至 URL

  4. 在 [Azure 儲存體容器] 下拉式清單中選取您在上一個步驟中建立的容器。Select the container you created in the previous step in the Azure storage container drop-down.

    Azure 儲存體容器

  5. 選取 [備份資料庫] 精靈上的 [確定] 以備份您的資料庫。Select OK on the Back Up Database wizard to back up your database.

  6. 成功備份資料庫之後,請選取 [確定] 以關閉所有備份相關視窗。Select OK once your database is backed up successfully to close all backup-related windows.

    提示

    您可以透過選取 [備份資料庫] 精靈頂端的 [指令碼] 來建立此命令背後的 Transact-SQL:指令碼命令You can script out the Transact-SQL behind this command by selecting Script at the top of the Back Up Database wizard: Script command

刪除資料庫Delete database

在此步驟中,請先刪除資料庫,然後再執行還原。In this step, delete the database before performing the restore. 此步驟僅適用於此教學課程的目的,在一般資料庫管理程序中不太可能使用。This step is only necessary for the purpose of this tutorial, but is unlikely to be used in normal database management procedures. 您可以跳過此步驟,但是在受控執行個體上進行還原時,您將需要變更資料庫的名稱,或執行還原命令 WITH REPLACE,以便在內部部署環境中成功還原資料庫。You can skip this step, but then you'll either need to change the name of the database during the restore on a managed instance, or run the restore command WITH REPLACE to restore the database successfully on-premises.

  1. 展開 [物件總管] 中的 [資料庫] 節點、以滑鼠右鍵按一下 []SQLTestDB 資料庫,然後選取刪除以啟動 [刪除物件] 精靈。Expand the Databases node in Object explorer, right-click the SQLTestDB database, and select delete to launch the Delete object wizard.
  2. 在受控執行個體上,選取 [確定] 以刪除資料庫。On a managed instance, select OK to delete the database. 在內部部署環境中,選取 [關閉現有的連接] 旁的核取方塊並選取 [確定] 以刪除資料庫。On-premises, check the checkbox next to Close existing connections and then select OK to delete the database.

對話方塊的Restore database

在此步驟中,使用 SQL Server Management Studio 中的 GUI 或使用 Transact-SQL 來還原資料庫。In this step, restore the database using either the GUI in SQL Server Management Studio, or with Transact-SQL.

  1. 以滑鼠右鍵按一下 SQL Server Management Studio 內 [物件總管] 中的 [資料庫] 節點,然後選取 [還原資料庫] 。Right-click the Databases node in Object Explorer within SQL Server Management Studio and select Restore Database.

  2. 選取 [裝置] ,然後選取省略符號 (...) 以選擇裝置。Select Device and then select the ellipses (...) to choose the device.

    選取還原裝置

  3. 從 [備份媒體類型] 下拉式清單選取 [URL] 並選取 [新增] 以新增您的裝置。Select URL from the Backup media type drop-down and select Add to add your device.

    新增備份裝置

  4. 從下拉式清單選取容器,然後貼到您在建立認證時儲存的共用存取簽章 (SAS)。Select the container from the drop-down and then paste in the Shared Access Signature (SAS) you saved when creating the credential.

    備份目的地

  5. 選取 [確定] 以選取備份檔案位置。Select OK to select the backup file location.

  6. 展開 [容器] 並選取您的備份檔案所在的容器。Expand Containers and select the container where your backup file exists.

  7. 選取您要還原的備份檔案,然後選取 [確定] 。Select the backup file you want to restore and then select OK. 如果看不到任何檔案,則您可能使用了錯誤的 SAS 金鑰。If no files are visible, then you may be using the wrong SAS key. 您可以遵循與先前相同的步驟來重新產生 SAS 金鑰,以新增容器。You can regenerate the SAS key again by following the same steps as before to add the container.

    選取還原檔案

  8. 選取 [確定] 以關閉 [選取備份裝置] 對話方塊。Select OK to close the Select backup devices dialog box.

  9. 選取 [確定] 以還原您的資料庫。Select OK to restore your database.

另請參閱See also

下列是一些建議閱讀的主題,這些主題可讓您了解針對 SQL ServerSQL Server 備份使用 Azure Blob 儲存體服務的概念與最佳做法。Following is some recommended reading to understand the concepts and best practices when using Azure Blob storage service for SQL ServerSQL Server backups.