azure blob_如何从Azure BLOB存储连接和执行SQL Server数据库还原

最新推荐文章于 2021-05-07 10:55:36 发布

culuo4781

最新推荐文章于 2021-05-07 10:55:36 发布

阅读量635

点赞数

文章标签：数据库 java python 大数据 linux

原文链接：https://www.sqlshack.com/how-to-connect-and-perform-a-sql-server-database-restore-from-azure-blob-storage/

版权

azure blob

Having things in the cloud should make life simpler but I have experienced it’s not that straightforward. Once all access / configuration is sorted out then yes, of course! But in the meantime it can be tedious (even frustrating) and the end result is something that could have been achieved with a different method.

将事物存储在云中应该可以使生活更简单，但是我已经体验到了这并不那么简单。确定所有访问/配置后，当然可以！但是与此同时，这可能很繁琐（甚至令人沮丧），并且最终结果是可以通过其他方法实现的。

I think a significant chunk of what Azure offers is easier to do with the more conventional methods but that separates us from the advanced Azure features that a company would like to utilize in the shorter / longer term future. So there are reasons to spend some time / effort in getting things right up there.

我认为，Azure提供的大部分功能都可以通过更传统的方法轻松实现，但这使我们与公司希望在短期/长期的未来中使用的高级Azure功能区分开。所以，我们有理由花一些时间/精力把事情做对了没有 。

In my case the task I needed to accomplish sounded simple enough:

就我而言，我需要完成的任务听起来很简单：

get a SQL Server database backup (.bak file) from Azure blob storage
从Azure Blob存储中获取SQL Server数据库备份（.bak文件）
copy it to our own environment and restore it to a SQL Server instance on an Azure Windows VM
将其复制到我们自己的环境中，并将其还原到Azure Windows VM上SQL Server实例
or skip the copy step and restore straight from blob storage
或跳过复制步骤并直接从Blob存储中还原

概念 (Concepts)

Names and concepts in Azure can be very different from on premise nomenclature so my advice is to always check the actual, up-to-date (fingers crossed) documentation that’s available on the official Microsoft websites to make sure your understanding is aligned with theirs. It’s not a question of common sense or being smart, just be aware that every nuance can make a difference!

Azure中的名称和概念可能与内部术语大不相同，因此我的建议是始终检查Microsoft官方网站上提供的实际，最新的（ 交叉手指 ）文档，以确保您的理解与他们的理解保持一致。这不是常识或聪明的问题，只要注意每一个细微差别都能有所作为！

(I will simplify things here as your true source of information should be the official documentation so I am very conveniently allowing myself to be somewhat casual ;-))

（ 我将在这里简化一下，因为您的真实信息来源应该是官方文档，因此我非常方便地让自己比较随意 ；-））

Storage account

储存帐号

There is (can be) a difference between a storage account and a blob storage account! Though you can hear people talking about them as actual synonyms. A little childhood-like way to remember it: every blob storage account is a storage account but not all storage account is a blob storage account.

存储帐户和Blob存储帐户之间（可能）有所不同！尽管您可以听到人们在谈论它们是实际的同义词。一种类似于童年时代的记忆方式： 每个Blob存储帐户都是一个存储帐户，但并非所有存储帐户都是Blob存储帐户 。

As I think of it, the name actually shows that blob storage account is a sub-concept of storage account.

在我看来，该名称实际上表明Blob存储帐户是存储帐户的一个子概念。

But what actually a storage account is? It is basically a general digital space that you can store (what a surprise!) your data. There are four different types of storage at the same time (i.e. the same storage account can have all or any combination of these):

但是实际上是什么存储帐户？这基本上是可以存储（ 一个惊喜！）您的数据的通用数字空间。同时有四种不同类型的存储（即，相同的存储帐户可以具有所有这些或任何组合）：

blob container – for large, unstructured data
Blob容器–用于大型非结构化数据
file shares – it is like an online external hard drive
文件共享–就像在线外接硬盘一样
queues – large number of small messages in a queue
队列–队列中的大量小消息
tables – large amount of structured data in a NoSQL database
表– NoSQL数据库中的大量结构化数据

In the case of general storage account, Azure Storage Explorer shows it as External. There is a way to provision a storage account straight as blob:

对于常规存储帐户，Azure Storage Explorer将其显示为“ 外部” 。有一种方法可以直接将存储帐户设置为blob：

I “hear” the questions being formed in your heads: what the heck could this v1 and v2 two be? Despite the similarities this is not the long-range German rocket from WW II!

我“听到”了在您脑海中形成的问题：这两个v1和v2到底有什么用？尽管有相似之处，但这并不是第二次世界大战的远程德国火箭！

As a rule of thumb use V2 if the price is inside your budget. V1 and blob are becoming a legacy feature.

根据经验，如果价格在预算范围内，请使用V2。 V1和Blob正在成为旧功能。

Blob

斑点

“Blobs are basically files like those that you store on your computer” – says Microsoft. They say a lot more in a simple way, so head there for additional details!

“ Blob基本上是与您存储在计算机上的文件类似的文件 ” – 微软说。他们以简单的方式说了很多话，所以请前往此处获取更多详细信息！

So blob is not the container or the storage or something else, blob is the file itself! I heard different people having a different understanding about this but when it comes to coding you have to know how all this is structured. What a project manager can get away with during a meeting, the scripting environment won’t let you get off the hook that easily!

因此，blob不是容器，存储或其他东西，blob是文件本身！我听到不同的人对此有不同的理解，但是在编码方面，您必须知道所有这些的结构。项目经理在会议期间可以避免的事情是，脚本环境不会让您轻易摆脱困境！

Structure

结构体

How to reference a blob? Since it is an online object you need a URL that points to it and a key that gives you access. That URL is put together of the following 3 parts:

如何引用斑点？由于它是一个在线对象，因此您需要一个指向它的URL和一个可以访问的密钥。该URL由以下3部分组成：

storage account
储存帐户
container name
容器名称
blob (file) name
Blob（文件）名称

The URL always starts with https and after the name of the storage account (which is custom given by the person provisioning the service) it always has the .blob.core.windows.net suffix. Please find below an example:

URL始终以https开头，并在存储帐户名称（由提供服务的人员自定义）之后，它始终带有.blob.core.windows.net后缀。请在下面找到一个例子：

插曲＃1 (Interlude #1)

Let me describe my journey (isn’t this on overused word nowadays?) as I went through the steps:

让我描述一下我的旅程（现在不是用过度使用的词吗？）：

连接到Azure存储帐户 (Connecting to Azure storage account)

It sounds simple, isn’t it? And it is … it should have been… if the storage account was originally set up correctly. In my case it didn’t happen and since a 3rd party was responsible for setting it up I was only given a URL and a key, nothing else.

听起来很简单，不是吗？如果最初正确设置了存储帐户，则应该……应该。就我而言，这没有发生，并且由于由第三方负责进行设置，因此我仅获得一个URL和一个密钥 ，没有别的。

I tried connecting various ways, but I couldn’t make it work. Yet it gave me a good lesson to explore these different ways:

我尝试通过各种方式进行连接，但无法使其正常工作。但是，这给了我一个很好的教训，探讨了这些不同的方式：

PowerBI / Excel – I put them as one option as behind the scenes it is the same

PowerBI / Excel –我将它们作为一种选择，就像在幕后一样

Almost the same using Power BI (I think behind the scenes it can actually be the same with a different visual theme):

使用Power BI几乎相同（我认为在幕后对于不同的视觉主题实际上可以是相同的）：

Microsoft Azure Storage Explorer

Microsoft Azure存储资源管理器

This is a handy tool provided by Microsoft for free. It doesn’t do much but does that quite effectively. You can download it.

这是Microsoft免费提供的一种便捷工具。它没有做太多，但是却相当有效。您可以下载它。

Provide the URL and the key (of course when a proper key is entered the red warning message disappears – or should disappear)

提供URL和密钥（当然，输入正确的密钥后，红色警告消息会消失-或应该消失）

If everything is configured correctly, after clicking next you should have access to the storage:

如果一切配置正确，则单击下一步后，您应该可以访问该存储：

Note the External next to the obfuscated storage account name! See details in the Concept at the beginning!

注意混淆的存储帐户名称旁边的“ 外部” ！开始时请参阅概念中的详细信息！

This utility is a file manager at the same time, not just a simple explorer:

该实用程序同时是一个文件管理器，而不仅仅是一个简单的资源管理器：

AzCopy

This is a command line executable also provided by Microsoft. It needs to be installed first, the official documentation you can Bing it (if I want to be politically correct…), Google it (if I want to conform to the minority, which I don’t) or – as I prefer – duck it (using Duck Duck Go). Or just simply click this link

这也是Microsoft提供的命令行可执行文件。它需要首先安装，如果需要，您可以在它的官方文档中使用Bing（如果我要在政治上正确的话……），Google（如果我要遵守少数人的要求，我不愿意）或-Duck（我更喜欢）-Duck （使用Duck Duck Go ）。或者只需单击此链接

It is a very handy tool and nicely documented plus a lot of info is available about it online. A very simple sample (for a tongue-twister say this simple sample 3/6/x times) is like that:

这是一个非常方便的工具，文档完善，在线上也有很多可用的信息。一个非常简单的示例（对于一次绕口令来说，这个简单的示例是3/6 / x次）是这样的：

AzCopy /Dest:path_for_local_file /Source:path_for_blob /SourceKey:key_provided_for_storage_account

AzCopy /目的地：path_for_local_file /来源：path_for_blob / SourceKey：key_provided_for_storage_account

For details about path_for_blob scroll up to just before the first interlude!

有关 path_for_blob的 详细信息， 请 滚动到第一个插曲之前！

PowerShell

电源外壳

PowerShell is great! I think on the long run it is superior to AzCopy due its versatility as a scripting engine! Setting up all the modules and cmdlets can be a pain but once that is done and you’ve figured out how to manipulate these Azure objects in a few hours (haha), it works like a charm! Jokes aside it is very worthwhile to be familiar with PowerShell scripting when it comes to Azure!!!

PowerShell很棒！我认为，从长远来看，它作为脚本引擎的多功能性要优于AzCopy！设置所有模块和cmdlet可能很麻烦，但是一旦完成，您就已经知道如何在几个小时内（haha）操纵这些Azure对象，它就像一个魅力！除了玩笑，在谈到Azure时，熟悉PowerShell脚本是非常值得的！！！

So here is a little script that downloads a file (blob) from an Azure blob storage using parameters. The important line is the very last one.

因此，这里有一个小脚本，可使用参数从Azure Blob存储中下载文件（blob）。重要的一行是最后一行。

#source-related parameters
    $SourceStorageAccount = "the url without the https:// part"
    $SourceStorageKey = "the key"
    $SourceStorageContainer = 'container name'
    $SourceStorageContext = New-AzureStorageContext –StorageAccountName $SourceStorageAccount -StorageAccountKey $SourceStorageKey
    $SourceFileName = "name of source file.ext"
 
#destination parameters
    $DestinationFolder = "path to local folder"
    $DestinationSuffix = $DestinationSuffix = Get-Date -Format "yyyymmdd_HHMM"
    $DestinationFileName = $SourceFileName.Replace(".bak", "_" + $DestinationSuffix + ".bak")
 
#list contents
    #Get-AzureStorageContainer -Context $SourceStorageContext -Name * | Get-AzureStorageBlob
    #Get-AzureStorageBlob -Container $SourceStorageContainer -Context $SourceStorageContext | Select Name
 
#download files / objects
    Get-AzureStorageBlobContent -blob $SourceFileName -container $SourceStorageContainer -destination $DestinationFolder -context $SourceStorageContext

I used RobinDotNet’s very clear and easy-to-understand (and working!!!) blog entry.

我使用了RobinDotNet的非常清晰且易于理解（并且可以正常工作！！）的博客条目。

His blog has a wealth of Azure-knowledge a good starting point for PowerShell operations in the Microsoft cloud.

他的博客具有丰富的Azure知识，是Microsoft云中PowerShell操作的良好起点。

This other entry also helped me quite a lot

这另一个条目也对我有很大帮助

The official documentation about the different Azure PS commands can be very handy, it’s worth a bookmarking. Specifically about Get-AzureStorageBlobContent

有关不同的Azure PS命令的官方文档可能非常方便，值得添加书签。专门关于Get-AzureStorageBlobContent

插曲＃2 (Interlude #2)

So we have figured out how to get access to the blob (the file!) and download it to a local folder. The next step is restoring the .bak database backup. If to be done from that local file, my post would end here. But there is a way to restore directly from inside the storage account blob container without downloading the file first.

因此，我们已经找到了如何访问Blob（文件！）并将其下载到本地文件夹的方法。下一步是还原.bak数据库备份。如果要从该本地文件完成操作，我的帖子将在此处结束。但是，有一种方法可以直接从存储帐户blob容器内部还原，而无需先下载文件。

Is that a straightforward and easy-to-use option? In theory: yes. In practice? Read on!

这是一个简单易用的选择吗？理论上：是的。在实践中？继续阅读！

直接从Blob还原数据库 (Restore database directly from blob)

First I thought there is a way SSMS can be used for that but I couldn’t make it work. It required quite a complicated setup … and my experiment ended with error messages. So instead let’s follow the T-SQL way!

首先，我认为有一种方法可以将SSMS用于此目的，但我无法使其工作。它需要相当复杂的设置……我的实验以错误消息告终。因此，让我们遵循T-SQL的方式！

Page Blob (no blocks please, this is not LEGO)

Page Blob（请无块，这不是乐高）

First of all there are three different types of blobs: block blobs, page blobs, append blobs. SQL Server only supports restore from URL if the blob’s type is page blob not block. Here’s the snippet from the official documentation:

首先，有三种不同类型的Blob：块Blob，页面Blob，附加Blob。如果Blob的类型是页面Blob而不是阻止，则SQL Server仅支持从URL还原。这是官方文档中的片段：

If you choose to copy and upload a backup file to the Windows Azure Blob storage service, use page blob as your storage option. Restores from Block Blobs are not supported. RESTORE from a block blob type fails with an error.

如果您选择将备份文件复制并上传到Windows Azure Blob存储服务，请使用page blob作为存储选项。不支持从块Blob还原。从块Blob类型进行还原失败，并显示错误。

Of course the blob I was granted access to was in Block Blob format… luckily there is an easy fix for that: AzCopy has a switch that is nice and easy for this very case

当然，我被授予访问权限的Blob是Block Blob格式……幸运的是，有一个简单的解决方法：AzCopy在这种情况下具有很好的开关

/BlobType:page

So once your blob has the correct type (it doesn’t affect the actual content of the file only how it is stored) you can go on with the actual restore!

因此，一旦您的Blob具有正确的类型（它不影响文件的实际内容，仅影响文件的存储方式），您就可以继续进行实际的还原！

从网址还原 (Restore from URL)

Starting point

初始点

Steps to follow

遵循的步骤

create credential that uses the Shared Access Signature
创建使用共享访问签名的凭据
another credential to authenticate to the blob storage container
另一个用于对Blob存储容器进行身份验证的凭据
restore the database
恢复数据库

Credentials

证书

Two of these are needed, one uses the storage account URL and the key that we used to connect to the blob container, the other one is to utilise the Shared Access Signature.

需要其中两个，一个使用存储帐户URL和我们用于连接到Blob容器的密钥，另一个使用共享访问签名。

Julie Koesmarno wrote a nice post about this 5 years ago but her solution still works! So don’t hesitate to check out that entry of hers:

朱莉·科斯马诺（Julie Koesmarno）在5年前写了一篇不错的文章，但她的解决方案仍然有效！因此，请不要犹豫查看她的条目：

The T-SQL commands to create them:

使用T-SQL命令创建它们：

--using the url and the key
CREATE CREDENTIAL [Credential_BLOB]
WITH IDENTITY= 'storage account url',
SECRET = 'storage account key';
 
--using the Shared Access Signature (SAS)
CREATE CREDENTIAL [Credential_Name_SAS]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'st=a very looooong key';

To find out the Shared Access Signature, the easiest way is to use Azure Storage Explorer:

要找出共享访问签名，最简单的方法是使用Azure存储资源管理器：

The pop up window allows you to configure the permissions you can assign to whatever uses this new signature you are about to create (Read / Write / Delete / List) and you can specify if it is blob or container level (to create a container level SAS, right-click on the actual container will also provide the option of selecting the Get Shared Access Signature… option.

弹出窗口允许您配置权限，您可以分配给将要使用的新签名（读取/写入/删除/列表）的权限，还可以指定是blob级别还是容器级别（创建容器级别） SAS，在实际容器上单击鼠标右键，还将提供选择“ 获取共享访问签名...”选项的选项。

When it’s created you’ll receive the URL (which you know anyway) and the Query string that is used as the secret for the SHARED ACCESS SIGNATURE identity in the second SQL query above.

创建完后，您将在上面的第二个SQL查询中收到URL（仍然知道）和查询字符串，该字符串用作SHARED ACCESS SIGNATURE标识的秘密。

The ? is not needed at the beginning, just start from the st=

？一开始不需要，只需从st =

恢复 (RESTORE)

Then finally we have arrived to the last step: the actual restore! This is again done using T-SQL instead of a GUI-based approach as many things related to Azure don’t have a proper GUI yet.

最后，我们到达了最后一步：实际还原！再次使用T-SQL而不是基于GUI的方法来完成此操作，因为与Azure相关的许多事情还没有合适的GUI。

RESTORE DATABASE Database_Name FROM URL = 'https://StorageAccount.blob.core.windows.net/Container/SampleDatabase.bak'
WITH CREDENTIAL = 'Credential_BLOB',
MOVE 'Database' to 'D:\DATABASES\SampleDatabase.mdf',
MOVE 'Database_log' to 'M:\LOGS\SampleDatabase.ldf'

And just when I thought I had it right… an error message popped up:

就在我以为自己做对了...弹出错误消息：

Msg 3268, Level 16, State 1, Line 17
Cannot use the backup file
‘https://StorageAccount.blob.core.windows.net/Container/SampleDatabase.bak’ because it was originally formatted with sector size 512 and is now on a device with sector size 65536.
Msg 3013, Level 16, State 1, Line 17
RESTORE DATABASE is terminating abnormally.

Msg 3268，第16级，状态1，第17行
无法使用备份文件
'https://StorageAccount.blob.core.windows.net/Container/SampleDatabase.bak'，因为它最初以扇区大小512进行格式化，现在已在扇区大小为65536的设备上使用。
Msg 3013，第16级，状态1，第17行
RESTORE DATABASE异常终止。

The key here is it was originally formatted with sector size 512 and is now on a device with sector size 65536.

此处的关键是它最初以扇区大小512进行格式化，现在已在扇区大小为65536的设备上使用。

There are two ways to solve it:

有两种解决方法：

on the source server back up the database again with the sector size of the target device (make it 65 536)
在源服务器上，使用目标设备的扇区大小再次备份数据库（使其达到65536）

RESTORE DATABASE Database_Name FROM URL = 'https://StorageAccount.blob.core.windows.net/Container/SampleDatabase.bak'
WITH CREDENTIAL = 'Credential_BLOB',
MOVE 'Database' to 'D:\DATABASES\SampleDatabase.mdf',
MOVE 'Database_log' to 'M:\LOGS\SampleDatabase.ldf',
BLOCKSIZE = 512

That command now properly restores the database. Drumroll and fanfare! I can now see and query the database in SSMS!

现在，该命令可以正确还原数据库。鼓声和夸奖！我现在可以在SSMS中查看和查询数据库！