This article discusses the concept of SQL Server backup and the various components required to use the Microsoft Azure Blob storage service as a target for the backup. As we all know, disk and tapes were the default destinations of choice until the evolution of the cloud platform. In addition to this, now we can expand the native functionality of SQL Server backup to cloud storage; the Windows Azure Blob storage. This functionality was added first in SQL Server 2012. In general, backup and restore functionality to and from the cloud are similar to using disk or tape, with very few differences.

本文讨论了SQL Server备份的概念以及使用Microsoft Azure Blob存储服务作为备份目标所需的各种组件。 众所周知，在云平台发展之前，磁盘和磁带是默认的选择目的地。 除此之外，现在我们可以将SQL Server备份的本机功能扩展到云存储。 Windows Azure Blob存储。 此功能是在SQL Server 2012中首先添加的。通常，与云之间的备份和还原功能与使用磁盘或磁带相似，差别很小。

SQL Server database Backup to Azure Blob Storage is a process designed to perform almost like a backup device, such as disk or tape. During the backup or restore process, a URL is selected as a “device type” which in turn triggers a VDI (Virtual Backup Device Interface) client process. The process acts as an intermediary agent to send the database backup to the Azure Blob Storage.

SQL Server数据库到Azure Blob存储的备份是一个旨在像磁盘或磁带之类的备份设备一样执行的过程。 在备份或还原过程中，将URL选择为“设备类型”，这又会触发VDI（虚拟备份设备接口）客户端过程。 该过程充当中介代理，以将数据库备份发送到Azure Blob存储。

In this article we touch base on the following topics:

在本文中，我们基于以下主题进行探讨：

1. Creating an Azure Storage account
   创建一个Azure存储帐户
2. Configuring Storage Container
   配置存储容器
3. Creating a credential for authorization using SSMS and T-SQL
   使用SSMS和T-SQL创建用于授权的凭证
4. Using the GUI to Perform a database backup and restore
   使用GUI执行数据库备份和还原
5. Using a T-SQL script to backup and restore the database
   使用T-SQL脚本备份和还原数据库
6. Implementing the backup-and-restore functionality using PowerShell
   使用PowerShell实施备份和还原功能
7. And more…
   和更多…

Before we perform the database backup, it’s important to understand the Blob Storage Service and SQL Server key components, and their concepts. To start, let’s do a little prep work to setup Microsoft Azure Create an Azure Storage account. In the first step, we create a storage account at the Azure Portal. Using the Blob Storage Service, we configure the Azure Blob Storage Container.

在执行数据库备份之前，了解Blob存储服务和SQL Server关键组件及其概念非常重要。 首先，让我们做一些准备工作来设置Microsoft Azure创建Azure存储帐户。 第一步，我们在Azure门户上创建一个存储帐户。 使用Blob存储服务，我们配置Azure Blob存储容器。

Next, let’s review the SQL Server components.

接下来，让我们回顾一下SQL Server组件。

• The URL is a unique identifier used for every backup file.
  URL是用于每个备份文件的唯一标识符。
• A SQL Server credential is used for authenticating the resources. The backup and restore process must use this credential to authenticate the blob storage service and its associated containers.
  SQL Server凭据用于验证资源。 备份和还原过程必须使用此凭据来认证Blob存储服务及其关联的容器。
• In the last step, perform the backup to the cloud storage. The steps are almost the same as all the other backups, albeit with a few more configuration options.
  在最后一步中，执行到云存储的备份。 这些步骤与所有其他备份几乎相同，尽管有更多配置选项。

有关配置Azure Blob存储的分步说明 (Step-by-step instructions to configure Azure Blob Storage)

2. Storage accounts on the left pane, click 存储帐户 ”，单击“ Create storage accounts 创建存储帐户”。
   1. This section deals with creating the storage account to be able to opt for the required model which has a direct impact on the cost based on usage.
      本节介绍如何创建存储帐户，以便能够选择所需的模型，这将直接影响基于使用量的成本。
   2. sqlshackdemo. The next step is to assign this storage account to a resource group. We have two options here; either use an existing resource group or create a new one. Let’s create a new one for the demonstration and name it as sqlshackdemo 。 下一步是将此存储帐户分配给资源组。 这里有两个选择； 使用现有资源组或创建一个新资源组。 让我们为演示创建一个新名称，并将其命名为sqlshack. sqlshack 。
   3. Create button. Wait a minute or two for the storage account to be created.创建”按钮。 等待一两分钟以创建存储帐户。
      The following information is required to create a storage account:
      创建存储帐户需要以下信息：
      1. sqlshackdemo; a unique name, all lowercase letterssqlshackdemo; 唯一的名称，所有小写字母
      2. In this case, the default setting is used for the Account Kind
         在这种情况下，默认设置用于“帐户类型”
      3. Default Performance setting – Standard
         默认性能设置-标准
      4. Default setting – Storage service encryption – Enabled
         默认设置–存储服务加密–已启用
      5. Resource group – Create new resource group named sqlshack
         资源组–创建名为sqlshack的新资源组
      6. Select Location – East-US is selected
         选择位置–选择美国东部
      7. Pin to dashboard option固定到仪表板选项
      8. Create button创建按钮
   4. 1. Overview blade, select the option to create a blob storage. 概述”刀片中，选择选项以创建Blob存储。
      2. Blobs. It would open up a new window. 斑点” 。 它将打开一个新窗口。
      3. + container icon to create a new container. +容器图标创建一个新的容器。
      4. sqlshackbackup. Remember that the name has to be all lowercase letters. sqlshackbackup 。 请记住，名称必须全部为小写字母。
      5. Private as the 私有作为Access type 访问类型
      6. Create button.创建按钮。

Now, we can go over to SQL Server and start working on creating the credentials that will be used to authenticate into the Azure account. In the New query window, use CREATE CREDENTIAL to create credential.

现在，我们可以转到SQL Server并开始着手创建将用于向Azure帐户进行身份验证的凭据。 在“新建查询”窗口中，使用CREATE CREDENTIAL创建凭据。

The Identity property is the name of the storage account. In this case, it is sqlshackdemo.

Identity属性是存储帐户的名称。 在这种情况下，它是sqlshackdemo 。

The Access key is the secret key of the storage account. To find the access key, click on Settings and select Access keys in the Storage account blade. To the right, you should fine key1, which is the access key, select the Copy to clipboard icon. Save this key information somewhere safe. The syntax to create to credential is as follows:

访问密钥是存储帐户的秘密密钥。 要找到访问密钥，请单击“ 设置” ，然后在存储帐户刀片中选择访问密钥 。 在右侧，您应该选择key1 （这是访问键），然后选择Copy to剪贴板图标。 将此密钥信息保存在安全的地方。 创建凭证的语法如下：

IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = '
    
     ') CREATE CREDENTIAL 
     
      ] WITH IDENTITY = '
      
       ' ,SECRET = '
       
        ';
       
      
     
    

IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = 'SQLShackDemoCredential')CREATE CREDENTIAL SQLShackDemoCredentialWITH IDENTITY='sqlshackdemo', SECRET='NEIb+nrJSaZOQ+e8XKX0R+obWdX85InXKtuS8yAQ/L4Osx2uht/UnQM6eqqO89Pu7JY8EyWePbP/lDy5UviXCg=='

Now, Prepare the T-SQL to backup the database to the URL using blob storage account service and the access key. The following example initiates a full database backup of the SQLShackDemoATC database to the blob storage (URL+Storage+Container+filename).

现在，准备T-SQL以使用blob存储帐户服务和访问密钥将数据库备份到URL。 以下示例将SQLShackDemoATC数据库的完整数据库备份启动到Blob存储（URL + Storage + Container +文件名）。

BACKUP DATABASE SQLShackDemoATC  TO URL = 'https://sqlshackdemo.blob.core.windows.net/sqlshackbackupdemo/SQLShackDemoATC.bak'        WITH CREDENTIAL = 'SQLShackDemoCredential'       ,COMPRESSION      ,STATS = 5;

Validate the output by browsing the blob storage. We can see that the SQLShackDemoATC.bak file exists at the storage. We can browse the URL of the file and this pointer is used in the upcoming section to restore the database.

通过浏览Blob存储来验证输出。 我们可以看到存储中存在SQLShackDemoATC.bak文件。 我们可以浏览文件的URL，在接下来的部分中将使用此指针来还原数据库。

For the demo, I will be restoring the SQLShackDemoATC with a different name SQLShackDemoATC_Clone to the same SQL instance.

对于该演示，我将使用不同名称SQLShackDemoATC_Clone还原SQLShackDemoATC到同一SQL实例。

RESTORE DATABASE SQLShackDemoATC_Clone FROM URL = 'https://sqlshackdemo.blob.core.windows.net/sqlshackbackupdemo/SQLShackDemoATC.bak'   WITH MOVE 'SQLShackDemoATC' to 'f:\PowerSQL\SQLShackDemoATC_Clone.mdf'  ,MOVE 'SQLShackDemoATC_log' to 'f:\PowerSQL\SQLShackDemoATC_CloneLog.ldf'  ,NORECOVERY  ,REPLACE  ,STATS = 5;  GO

The following output shows that the database was restored and with the same number of tables as the source database. This proves that the restoration of the database was successful.

以下输出显示该数据库已还原，并且具有与源数据库相同的表数。 这证明数据库还原成功。

Perform Database backup task using SSMS (SQL Server Management Studio)

使用SSMS执行数据库备份任务（SQL Server Management Studio）

1. In the Object Explorer, browse the Databases, right-click on the database, go to Tasks, and then click Back Up…

   在对象资源管理器中，浏览数据库 ，右键单击数据库，转到“ 任务” ，然后单击“备份…”。

   

2. On the General page, go to the Destination section. At the Back up to: drop-down list, select URL, and then click Add. This will open the Select Backup Destination dialog box

   在“ 常规”页面上，转到“ 目标”部分。 在“ 备份到：”下拉列表中，选择“ URL” ，然后单击“ 添加” 。 这将打开“ 选择备份目标位置”对话框

   

3. Click on New container. This will open a Connect to Microsoft Subscription page.

   单击新容器 。 这将打开“ 连接到Microsoft订阅”页面。

   

4. At the Select Storage Account drop down, select the storage account. In this case, it’s sqlshackdemo.

   在“ 选择存储帐户”下拉列表中，选择存储帐户。 在这种情况下，它是sqlshackdemo 。

   

5. In a similar way go to Select Blob Container, and choose sqlshackbackupdemo

   以类似的方式转到Select Blob Container ，然后选择sqlshackbackupdemo

   

6. Click on OK.

   单击确定。

7. Back Up Database wizard window. At the destination field, we can see the URL of the backup file at the storage. 备份数据库”向导窗口中。 在目标字段中，我们可以在存储中看到备份文件的URL。
   

8. The next screen shows the progress of the backup.

   下一个屏幕显示备份进度。

   

9. The last screen displays a message stating that the backup was successful.

   最后一个屏幕显示一条消息，指出备份已成功。

   

We can see that the backup file was written to the blob storage. This shows us that the backup can also be taken using SSMS.

我们可以看到备份文件已被写入Blob存储。 这表明我们也可以使用SSMS进行备份。

Next, this lets us demonstrate the steps to perform database backup to URL using PowerShell.

接下来，这使我们演示了使用PowerShell执行将数据库备份到URL的步骤。

This section provides the PowerShell scripts to automate the database backup using the available PowerShell cmdlets.

本节提供了PowerShell脚本，以使用可用的PowerShell cmdlet自动执行数据库备份。

1. Backup-SqlDatabase—used to back-up the database
   Backup-SqlDatabase-用于备份数据库
2. Restore-SqlDatabase—used to restore the database backup
   Restore-SqlDatabase-用于还原数据库备份
3. New-SqlCredential—used to create the SQL credentials to authenticate at the Azure Blob Storage
   New-SqlCredential-用于创建SQL凭据以在Azure Blob存储上进行身份验证
4. Get-SqlCredential—used to fetch the existing credentials
   Get-SqlCredential-用于获取现有凭据

#List the Credential detail$credentialName = "SQLShackDemoCredential" # define the backup file location URL+Storage+Container+filename$backupFile = "https://sqlshackdemo.blob.core.windows.net/sqlshackbackupdemo/ProdSQLShackDemo.bak" # Initiate Backup using backup-sqldatabase cmdlet to URLBackup-SqlDatabase -ServerInstance hqdbt01\sql2017  -Database "ProdSQLShackDemo" -backupFile $backupFile -SqlCredential $credentialName -CompressionOption ON

摘要 (Summary)

The three different ways to back-up the database to URL have been explained with examples using SSMS, T-SQL and PowerShell.

使用SSMS，T-SQL和PowerShell的示例已说明了将数据库备份到URL的三种不同方法。

要记住的要点 (Points to remember)

1. It is recommended to use unique file names for every backup in order to prevent accidental overwriting of the blobs.
   建议为每个备份使用唯一的文件名，以防止意外覆盖Blob。
2. During the creation of the container, it is recommended that you set the access level to private so that only the users or accounts that can provide the required credentials can read or write to the blobs in the container.
   在创建容器的过程中，建议将访问级别设置为私有，以便只有能够提供所需凭据的用户或帐户才能读取或写入容器中的Blob。
3. For SQL Server databases on an instance of SQL Server running in a Windows Azure Virtual Machine, use a storage account in the same region as the virtual machine itself in order to avoid the costs incurred in transferring data between regions. Using the same region also ensures optimal performance of the backup and restore operations.
   对于在Windows Azure虚拟机中运行SQL Server实例上SQL Server数据库，请使用与虚拟机本身相同的区域中的存储帐户，以避免在区域之间传输数据时产生的费用。 使用相同的区域还可以确保备份和还原操作的最佳性能。
4. . 。
5. Using the WITH COMPRESSION option during backup can minimize your storage costs and storage transaction costs. It can also decrease the time taken to complete the backup process.
   在备份过程中使用WITH COMPRESSION选项可以最大程度地降低存储成本和存储事务处理成本。 它还可以减少完成备份过程所需的时间。

目录 (Table of contents)

SQL Server Database backup and restore operations using the Cloud

使用CloudSQL Server数据库备份和还原操作

参考资料 (References)

翻译自: