快速入門:SQL 備份及還原至 Azure Blob 儲存體服務Quickstart: SQL backup and restore to Azure Blob storage service
SQL Server
Azure SQL Database (僅限受控執行個體)
Azure Synapse Analytics (SQL DW)
平行處理資料倉儲
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:
開啟 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 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).
- 啟動 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. 在 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.
展開 SQL Server Management Studio(SSMS) 之 [物件總管] 內的 [資料庫] 節點。Expand the Databases node within Object Explorer of SQL Server Management Studio(SSMS).
以滑鼠右鍵按一下新的
SQLTestDB
資料庫,將滑鼠游標移到 [工作] 上方並選取 [備份] 以啟動 [備份資料庫] 精靈。Right-click your newSQLTestDB
database, hover over Tasks and then select Back up... to launch the Back Up Database wizard.從 [備份至] 目的地下拉式清單選取 [URL] ,然後選取 [新增] 以啟動 [選取備份目的地] 對話方塊。Select URL from the Back up to destination drop-down, and then select Add to launch the Select Backup Destination dialog box.
選取 [選取備份目的地] 對話方塊上的 [新增容器] 以啟動 [連線至 Microsoft 訂用帳戶] 視窗。Select New container on the Select Backup Destination dialog box to launch the Connect to a Microsoft Subscription window.
選取 [登入] 以登入 Azure 入口網站,然後繼續完成登入程序。Sign in to the Azure portal by selecting Sign In... and then proceed through the sign-in process.
從下拉式清單選取您的訂用帳戶。Select your subscription from the drop-drown.
從下拉式清單中選取您的儲存體帳戶。Select your storage account from the drop-down.
從下拉式清單選取您先前建立的容器。Select the container you created previously from the drop-down.
選取 [建立認證] 以產生您的共用存取簽章 (SAS) 。Select Create Credential to generate your Shared Access Signature (SAS). 儲存此值,因為您將需要它才能進行還原。Save this value as you'll need it for the restore.
選取 [確定] 以關閉 [連線至 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.
目前,您可以跳到下一節中的步驟 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).
若 [備份資料庫] 精靈尚未開啟,請展開 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).
以滑鼠右鍵按一下新的
SQLTestDB
資料庫,將滑鼠游標移到 [工作] 上方並選取 [備份] 以啟動 [備份資料庫] 精靈。Right-click your newSQLTestDB
database, hover over Tasks and then select Back up... to launch the Back Up Database wizard.從 [備份至] 下拉式清單選取 [URL] ,然後選取 [新增] 以啟動 [選取備份目的地] 對話方塊。Select URL from the Back up to drop down, and then select Add to launch the Select Backup Destination dialog box.
在 [Azure 儲存體容器] 下拉式清單中選取您在上一個步驟中建立的容器。Select the container you created in the previous step in the Azure storage container drop-down.
選取 [備份資料庫] 精靈上的 [確定] 以備份您的資料庫。Select OK on the Back Up Database wizard to back up your database.
成功備份資料庫之後,請選取 [確定] 以關閉所有備份相關視窗。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:
刪除資料庫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.
- 展開 [物件總管] 中的 [資料庫] 節點、以滑鼠右鍵按一下 []
SQLTestDB
資料庫,然後選取刪除以啟動 [刪除物件] 精靈。Expand the Databases node in Object explorer, right-click theSQLTestDB
database, and select delete to launch the Delete object wizard. - 在受控執行個體上,選取 [確定] 以刪除資料庫。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.
以滑鼠右鍵按一下 SQL Server Management Studio 內 [物件總管] 中的 [資料庫] 節點,然後選取 [還原資料庫] 。Right-click the Databases node in Object Explorer within SQL Server Management Studio and select Restore Database.
選取 [裝置] ,然後選取省略符號 (...) 以選擇裝置。Select Device and then select the ellipses (...) to choose the device.
從 [備份媒體類型] 下拉式清單選取 [URL] 並選取 [新增] 以新增您的裝置。Select URL from the Backup media type drop-down and select Add to add your device.
從下拉式清單選取容器,然後貼到您在建立認證時儲存的共用存取簽章 (SAS)。Select the container from the drop-down and then paste in the Shared Access Signature (SAS) you saved when creating the credential.
選取 [確定] 以選取備份檔案位置。Select OK to select the backup file location.
展開 [容器] 並選取您的備份檔案所在的容器。Expand Containers and select the container where your backup file exists.
選取您要還原的備份檔案,然後選取 [確定] 。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.
選取 [確定] 以關閉 [選取備份裝置] 對話方塊。Select OK to close the Select backup devices dialog box.
選取 [確定] 以還原您的資料庫。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.
意見反應
正在載入意見反應...