快速入门:将 SQL 备份和还原到 Azure Blob 存储服务Quickstart: SQL backup and restore to Azure Blob storage service

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance

本快速入门可帮助你了解如何将备份写入 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.

备注

SQL Server 2012 SP1 CU2 引入了对备份到 Azure Blob 存储的支持。SQL Server 2012 SP1 CU2 introduced support for back up to Azure Blob storage. SQL Server 2014 及更早版本不支持本快速入门文章中介绍的共享访问签名 (SAS)。SQL Server 2014 and prior does not support the Shared Access Signature (SAS) that is described in this quickstart article.

对于 SQL Server 2014 及更早版本,请使用教程:SQL Server 2014 备份和还原到 Microsoft Azure Blob 存储For SQL Server 2014 and prior, use Tutorial: SQL Server 2014 Backup and Restore to Microsoft Azure Blob storage.

先决条件Prerequisites

要完成本快速入门,必须熟悉 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 托管实例的访问权限。You need an Azure storage account, SQL Server Management Studio (SSMS), and access to either a server that's running SQL Server or Azure SQL Managed Instance. 此外,用于发出 BACKUP 和 RESTORE 命令的帐户应属于具有“更改任意凭据”权限的 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. SQL 托管实例上新建的数据库会自动启用 TDE,因此需要禁用它才能继续操作。Newly created databases on SQL 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 SQL 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

以下是一些建议阅读的主题,便于了解概念以及在将 Azure Blob 存储服务用于 SQL ServerSQL Server 备份时的最佳做法。Following is some recommended reading to understand the concepts and best practices when using Azure Blob storage service for SQL ServerSQL Server backups.