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

• 04/09/2018
• • 
  • 
  • 
  • 

適用於： SQL Server Azure SQL Database (僅限受控執行個體) Azure Synapse Analytics (SQL DW) 平行處理資料倉儲 APPLIES TO: SQL Server Azure SQL Database (Managed Instance only) Azure Synapse Analytics (SQL DW) Parallel 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 帳戶。Get a free Azure Account.
• 建立 Azure 儲存體帳戶。Create an Azure storage account.
• 安裝 SQL Server Management Studio。Install SQL Server Management Studio.
• 安裝 SQL Server 2017 Developer Edition 或使用透過 Azure SQL 虛擬機器或點對站建立的連線來部署受控執行個體。Install SQL Server 2017 Developer Edition or deploy a managed instance with connectivity established through an Azure SQL virtual machine or point-to-site.
• 將使用者帳戶指派給 db_backupoperator 的角色，並授與 ALTER ANY CREDENTIAL 權限。Assign the user account to the role of db_backupoperator and grant 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.

   

建立測試資料庫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.

   

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

USE [master]

BACKUP DATABASE [SQLTestDB] 
TO  URL = N'https://msftutorialstorage.blob.core.windows.net/sql-backup/sqltestdb_backup_2020_01_01_000001.bak' 
WITH  COPY_ONLY, CHECKSUM
GO

刪除資料庫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.

USE [master]
GO
DROP DATABASE [SQLTestDB]
GO

-- If connections currently exist on-premises, you'll need to set the database into single user mode first
USE [master]
GO
ALTER DATABASE [SQLTestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SQLTestDB]
GO

對話方塊的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.

USE [master]
RESTORE DATABASE [SQLTestDB] FROM 
URL = N'https://msftutorialstorage.blob.core.windows.net/sql-backup/sqltestdb_backup_2020_01_01_000001.bak'

另請參閱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.

• 使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原SQL Server Backup and Restore with Microsoft Azure Blob Storage Service
• SQL Server 備份至 URL 的最佳作法和疑難排解SQL Server Backup to URL Best Practices and Troubleshooting