sql-server - 无法使用 PowerShell 或 T-SQL 将 SQL 数据库备份到 Azure Blob 存储

Question

我相信我正在按照 Microsoft 的文档将 SQL 数据库备份到 Azure Blob 存储；但是，无论我尝试什么，我都会遇到同样的错误。

例如，以下代码创建 SQL 凭据并尝试备份数据库。

运行它时，错误指出我无法使用WITH CREDENTIAL和SAS，但Microsoft在其文档中演示了直接使用两者 ( https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017#Examples )!

declare @databaseName varchar(50)
declare @destinationAzureStorageContainerPath varchar(256)
declare @destinationBlobPath varchar(256)
declare @timestampUtc as nvarchar(30)

select @timestampUtc = replace(convert(nvarchar(30), getutcdate(), 126), ':', '_');
set @databaseName = 'DWConfiguration'
set @destinationAzureStorageContainerPath = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/'
SET @destinationBlobPath = @destinationAzureStorageContainerPath + @databaseName + '_' + @timestampUtc + '.BAK'

if not exists
(select * from sys.credentials   
where name = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/')  
create CREDENTIAL [https://mystorageaccount.blob.core.windows.net/mystoragecontainer/] 
  with IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = 'sv... this is my token ...';

backup DATABASE @databaseName   
to URL = @destinationBlobPath
with CREDENTIAL = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/'
,COMPRESSION  
,STATS = 5

错误:

Msg 3225, Level 16, State 1, Line 28 Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature. Msg 3013, Level 16, State 1, Line 28 BACKUP DATABASE is terminating abnormally.

作为替代方法，我决定使用 PowerShell。

Backup-SqlDatabase -ServerInstance "myserver" -Database "DWConfiguration" -BackupFile "https://mystorageaccount.blob.core.windows.net/mystoragecontainer/mydatabase_2019-01-04T20_01_03.127.bak" -SqlCredential "https://mystorageaccount.blob.core.windows.net/mystoragecontainer/"

正如您所看到的，它会导致同样令人讨厌的错误!

Backup-SqlDatabase : System.Data.SqlClient.SqlError: Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.

在 blob 本身上，我设置了“私有(private)(无匿名访问)”。我只希望经过身份验证的请求能够访问该 blob。这可能是问题所在吗？如果是这样，为什么WITH CREDENTIAL不能解决这个问题？

如何简单地将 SQL 数据库的备份保存到我的 Azure 存储帐户？

引用号。 https://blog.sqlauthority.com/2018/07/17/sql-server-backup-to-url-script-to-generate-credential-and-backup-using-shared-access-signature-sas/

Answer 1

我认为您在 sql 脚本中犯了一个错误。

您创建了“使用共享访问签名”凭据，但在备份时使用了“使用存储帐户身份和访问 key 的到 URL”，该凭据与您之前创建的 sas 不匹配。

我在我身边测试了它，并且工作正常(创建凭据“使用共享访问签名”，并使用“使用共享访问签名到 URL”进行备份)。

IF NOT EXISTS (SELECT * FROM sys.credentials   
               WHERE name = 'https://xxx.blob.core.windows.net/container')  
CREATE CREDENTIAL [https://xxx.blob.core.windows.net/container] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = 'sv=xxx';


BACKUP DATABASE MyTest   
TO URL = 'https://xxx.blob.core.windows.net/container/123.bak'  
GO

测试结果如下: