快速入門：SQL Server 備份及還原至 Azure Blob 儲存體服務Quickstart: SQL Server Backup and Restore to Azure Blob Storage Service

04/09/2018

適用於： SQL Server Azure SQL Database Azure SQL 資料倉儲平行處理資料倉儲 APPLIES TO: yes SQL Server Azure SQL Database Azure SQL Data Warehouse 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 quickstart explains how to create an Azure Blob Container, credentials for accessing the storage account, writing a backup to the blob service, and then performing a simple 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 之伺服器的存取權，以及 AdventureWorks 資料庫。To use this quickstart, you need an Azure storage account, SQL Server Management Studio (SSMS), access to a server that's running SQL Server, and an AdventureWorks database. 此外，用來發出 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。Install SQL Server 2017 Developer Edition.
將使用者帳戶指派給 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. 帳戶可以包含不限數目的容器，但是至少必須具有一個容器。An 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.

我們會持續聽取您的意見： 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例)，請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

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

開啟 Azure 入口網站。Open the Azure portal.
巡覽至您的儲存體帳戶。Navigate to your Storage Account.
選取儲存體帳戶，向下捲動至 [Blob 服務] 。Select the storage account, scroll down to Blob Services.
選取 [Blob] ，然後選取 [+容器] 以新增新的容器。Select Blobs and then select +Container to add a new container.
輸入容器名稱，並記下您指定的容器名稱。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.
選取 [確定] 。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 programatically using REST APIs. 如需詳細資訊，請參閱建立容器For more information, see Create container

建立測試資料庫Create a test database

啟動 SQL Server Management Studio (SSMS) 並連線至 SQL Server 執行個體。Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
開啟 [新增查詢] 視窗。Open a New Query window.
執行下列 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.

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

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


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

建立 SQL Server 認證Create a SQL Server Credential

SQL Server 認證是用來儲存連接到 SQL Server 外部資源所需之驗證資訊的物件。A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. 此處，SQL Server 備份和還原程序會使用認證，向 Windows Azure Blob 儲存體服務驗證。Here, SQL Server backup and restore processes use credentials to authenticate with the Windows Azure Blob storage service. 認證會儲存儲存體帳戶的名稱以及儲存體帳戶的 存取金鑰 值。The Credential stores the name of the storage account and the storage account access key values. 一旦建立認證之後，您必須在發出 BACKUP/RESTORE 陳述式時，在 WITH CREDENTIAL 選項中指定認證。Once the credential is created, it must be specified in the WITH CREDENTIAL option when issuing the BACKUP/RESTORE statements. 如需認證的詳細資訊，請參閱認證。For more information about credentials, see Credentials.

重要

以下所述建立 SQL Server 認證的需求是特別針對 SQL Server 備份程序 (SQL Server 備份至 URL 及 SQL Server Managed Backup to Microsoft Azure) 的需求。The requirements for creating a SQL Server credential described below are specific to SQL Server backup processes (SQL Server Backup to URL, and SQL Server Managed Backup to Microsoft Azure). 在存取 Azure 儲存體以寫入或讀取備份時，SQL Server 會使用儲存體帳戶名稱與存取金鑰資訊。SQL Server uses the storage account name and access key information when accessing Azure storage to write or read backups.

存取金鑰Access keys

您將需要儲存體帳戶的存取金鑰，才可建立認證。You will need the access keys for the storage account to create the credential.

巡覽至 Azure 入口網站的 [儲存體帳戶] 。Navigate to the Storage Account in the Azure portal.
選取 [設定] 下的 [存取金鑰] 。Select Access Keys under Settings.
儲存金鑰和連接字串，以便稍後於本快速入門中使用。Save both the key and connection string to use later in this quickstart.

建立 SQL Server 認證Create a SQL Server credential

使用您儲存的存取金鑰，遵循下列步驟以建立 SQL Server 認證。Using the access key you saved, create the SQL Server credential following the steps below.

使用 SQL Server Management Studio 連線到 SQL ServerConnect to your SQL Server using SQL Server Management Studio.
選取 SQLTestDB 資料庫，並開啟 [新增查詢] 視窗。Select the SQLTestDB database and open a New Query window.

將下列範例複製並貼入查詢視窗中，視需要修改並執行：Copy, paste, and execute the following example into the query window, modifying as needed:

CREATE CREDENTIAL mycredential   
WITH IDENTITY= 'msftutorialstorage', -- this is the name of the storage account you specified when creating a storage account   
SECRET = '<storage account access key>' -- this should be either the Primary or Secondary Access Key for the storage account

執行陳述式以建立認證。Execute the statement to create the credential.

將資料庫備份至 Windows Azure Blob 儲存體服務Back up database to the Windows Azure Blob Storage Service

在本節中，您將使用 T-SQL 陳述式來執行 Windows Azure Blob 儲存體服務的完整資料庫備份。In this section, you will use a T-SQL statement to perform a full database backup to the Windows Azure Blob Storage service.

使用 SQL Server Management Studio 連線到 SQL ServerConnect to your SQL Server using SQL Server Management Studio.
選取 SQLTestDB 資料庫，並開啟 [新增查詢] 視窗。Select the SQLTestDB database and open a New Query window.

將下列範例複製並貼入查詢視窗中，並視需要修改：Copy and paste the following example into the query window, modifying as needed:

BACKUP DATABASE [SQLTestDB] 
TO URL = 'https://msftutorialstorage.blob.core.windows.net/sql-backup/SQLTestDB.bak' 
/* URL includes the endpoint for the BLOB service, followed by the container name, and the name of the backup file*/ 
WITH CREDENTIAL = 'mycredential';
/* name of the credential you created in the previous step */ 
GO

執行陳述式，以將 SQLTestDB 資料庫備份至 URL。Execute the statement to back up your SQLTestDB database to URL.

從 Windows Azure Blob 儲存體服務還原資料庫Restore database from Windows Azure Blob Storage Service

在本節中，您將使用 T-SQL 陳述式，還原完整資料庫備份。In this section, you will use a T-SQL statement to restore the full database backup.

使用 SQL Server Management Studio 連線到 SQL ServerConnect to your SQL Server using SQL Server Management Studio.
開啟 [新增查詢] 視窗。Open a New Query window.
將下列範例複製並貼入查詢視窗中，並視需要修改：Copy and paste the following example into the query window, modifying as needed:

RESTORE DATABASE [SQLTestDB] 
FROM URL = 'https://msftutorialstorage.blob.core.windows.net/sql-backup/SQLTestDB.bak' 
WITH CREDENTIAL = 'mycredential',
STATS = 5 -- use this to see monitor the progress
GO

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