尝试备份到Blob时，SQL 2016 IaaS的维护备份计划失败. [英] maintenance backup plan fails for SQL 2016 IaaS when trying to backup to a Blob.

问题描述

我正在尝试对sql 2016(windows 2016)IaaS使用备份维护计划，并将这些SQL备份放在Blob中.

I am trying to use a backup maintenance plan for sql 2016(windows 2016) IaaS and place these SQL backups in a Blob.

下面的SQL查询成功运行，并且我能够成功地手动备份到  Blob.但是，当我创建维护备份计划(仅配置了完整备份)时，它将失败，并在此sql查询下面显示错误.  

The below SQL query runs successfully and I am able to do a manual backup successfully to the  blob.  However when I create a maintenance backup plan ( only a full backup is configured ) it fails with the error below this sql query.   

 

创建凭据[ https://storageaccountXXXzz. blob.core.xxbbbcloudapi.net/ containernameXX ]  

CREATE CREDENTIAL [https://storageaccountXXXzz.blob.core.xxbbbcloudapi.net/containernameXX]  

WITH IDENTITY ='Shared Access Signature'，

WITH IDENTITY = 'Shared Access Signature', 

SECRET ='sv = 2017XXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXX'; <跨度样式="text-decoration:underline">

SECRET = 'sv=2017XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

GO

XXXXXXXXXXXXXXX

失败:(-1073548784)执行查询"BACKUP DATABASE [Database Test] TO  URL = N' https://storage. ..""失败，并显示以下错误:使用WITH CREDENTIAL语法对于包含共享访问签名的凭据无效.

Failed:(-1073548784) Executing the query "BACKUP DATABASE [Database Test] TO  URL = N'https://storage..." failed with the following error: "Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.

备份数据库异常终止..可能的失败原因:查询"ResultSet"存在问题.属性设置不正确，参数设置不正确或连接建立不正确.

XXXXXXXXXXXXXXXXXX

XXXXXXXXXXXXXXXXXX

我已经从容器属性中记录了URL，所以我知道这是正确的.

I have recorded the URL from the container properties so I know this is correct.

生成SAS令牌，然后将其复制到查询中.

SAS token was generated then copied into the query.

创建备份维护计划时，我在常规标签上选择URL. 目标"选项卡具有SQL信用，Azure存储容器，URL前缀和备份扩展名.

When creating the backup maintenance plan I choose URL on the general tab.  Destination tab has SQL cred, Azure storage container, URL prefix, and backup extension.

SQL Cred =有一个下拉菜单，所以我可以选择相应的选择.

SQL Cred = there is a pull down so I can choose the relevant choice.

Azure存储容器=当我输入URL前缀时，它将自动使用容器名称填充.

Azure storage container = this populates automatically with the container name when I enter the URL prefix.

URL前缀: https://storageaccountXXXzz. blob.core.xxbbbcloudapi.net/容器名称XX

我们如何解决这个问题?   

How do we fix this??  

推荐答案

用于发出BACKUP或RESTORE命令的用户帐户应位于db_backup操作员数据库角色中，且具有更改任何凭据"权限.

SQL Server requires that either a Windows Azure account name and access key authentication or a Shared Access Signature and access token be stored in a SQL Server Credential. This information is used to authenticate to the Windows Azure account when performing backup or restore operations. The user account that is used to issue BACKUP or RESTORE commands should be in the db_backup operator database role with Alter any credential permissions.

URL用于提供SQL Server备份文件的位置和名称.该URL必须指向实际的Blob，而不仅仅是容器. 如果Blob不存在，则会创建它.如果指定了现有Blob，则除非指定"WITH FORMAT"选项来覆盖Blob中的现​​有备份文件，否则BACKUP都会失败.

The URL is used to provide the location and name of the SQL Server backup file. The URL must point to an actual blob, not just a container. If the blob does not exist, it is created. If an existing blob is specified, BACKUP fails, unless the "WITH FORMAT" option is specified to overwrite the existing backup file in the blob.

这是示例网址值:

Here is a sample URL value:

http[s]://ACCOUNTNAME.blob.core.windows.net/<CONTAINER>/<FILENAME.bak>.

不需要HTTPS，但推荐.
of 使用维护计划向导将SQL Server备份到URL .

HTTPS is not required, but is recommended.

For more information, suggest you to refer the following document for better understanding of SQL Server Backup to URL Using Maintenance Plan Wizard.

If this answer was helpful, click "Mark as Answer" or "Up-Vote". To provide additional feedback on your forum experience, click here

这篇关于尝试备份到Blob时，SQL 2016 IaaS的维护备份计划失败.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！