How to use Backup-SqlDatabase (PowerShell) with SqlCredential created using storage account name and access key?

Question

I'm writing a script to backup databases on SQL Server 2019 Express to Azure Storage account. I created a SqlCredential named Cred1 using account name and key. Cred2 is created automatically using Shared Access Signature in SSMS.

In SSMS, I can backup database using Cred2 via GUI, or T-SQL using Cred1.

When I use PowerShell and ran following commands:

Set-Location SQLServer:\SQL\127.0.0.1\DEFAULT  
$cred = Get-SqlCredential -Name "Cred1"  
Backup-SqlDatabase -Database "<DatabaseName>" -BackupContainer "https://<StorageAccountName>.blob.core.windows.net/<ContainerName>" -SqlCredential $cred  

It display the error:

Backup-SqlDatabase : System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "https://<AccountName>.blob.core.windows.net/<ComputerName>-MSSQL15.MSSQLSERVER-<DatabaseName>-133120207153903930.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (404) Not Found..
At line:1 char:1
+ Backup-SqlDatabase -Database "<DatabaseName>" -BackupContainer "https://<Stor ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Backup-SqlDatabase], SmoException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

How to fix this?

Answer 1

Hi @vinixwu ,

The command you used, it uses BackupContainer, the name of the backup file is auto-generated, BackupContainer parameter can auto-generated file name for the specified location

From this doc, you can see the explanation in example 11
https://learn.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @vinixwu , 404 error mostly indicates the issue with backup container name /path provided.

Can you just refer SQL Server back up to URL for Microsoft Azure Blob Storage best practices and troubleshooting

Hope this helps you.

Thank You!

Regards,
Preetha

Answer 3

Hi @vinixwu

as Preetha already mentioned, it has something to do with your path...

And according to your snippet above, you don't name a container just the filename...

Backup-SqlDatabase -Database "<DatabaseName>" -BackupContainer "https://<StorageAccountName>.blob.core.windows.net/<ContainerName>" -SqlCredential $cred

https://<AccountName>.blob.core.windows .net/<Comp uterName>-MSSQL15.MSSQLSERVER-<DatabaseName>-133120207153903930.bak :

change this to (e.g.)
https://<AccountName>.blob.core.windows .net/backup/<Comp uterName>-MSSQL15.MSSQLSERVER-<DatabaseName>-133120207153903930.bak

and even remove the : at the end (this maybe just a copy and paste error?)

Answer 4

Hi,

The 404 is likely due to the container not being specified correctly resulting in an incorrect path.

If you check the sys.credentials table on the SQL Instance you should have the path you need in the name column.

SELECT * FROM sys.credentials WHERE credential_identity = 'SHARED ACCESS SIGNATURE'  

e.g. https://<StorageAccountName>.blob.core.windows.net/<ContainerName>

With SQL Server 2019 and Cred2 using SHARED ACCESS SIGNATURE you can omit the credential.

Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupContainer "https://<StorageAccountName>.blob.core.windows.net/<ContainerName>/"  

Answer 5

I changed the command to:

PS SQLSERVER:\SQL\127.0.0.1\DEFAULT\Databases>Backup-SqlDatabase -Database "DatabaseName" -BackupContainer "https://StorageAccountName.blob.core.windows.net/ContainerName/" -CopyOnly  

or

PS SQLSERVER:\SQL\127.0.0.1\DEFAULT\Databases>Backup-SqlDatabase -Database "DatabaseName" -BackupContainer "https://StorageAccountName.blob.core.windows.net/ContainerName/DatabaseName-FullCopyOnly-20221107.bak" -CopyOnly  

, and the error became:

Backup-SqlDatabase : System.Data.SqlClient.SqlError: Cannot open backup device 'https://StorageAccountName.blob.core.windows.net/ContainerName/ComputerName-MSSQL15.MSSQLSERVER-DatabaseName-133122599838620925.bak'. Operating system error 50(The request is not supported.).
At line:1 char:1

• Backup-SqlDatabase -Database "DatabaseName" -BackupContainer "https://Stor ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidOperation: (:) [Backup-SqlDatabase], SmoException
• FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

This similar error as above also occurred when I ran following T-SQL in SSMS:

BACKUP DATABASE [DatabaseName] TO URL = N'https://StorageAccountName.blob.core.windows.net/ContainerName/DBName_20221107.bak' WITH COPY_ONLY, NOREWIND  
GO  

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'https://StorageAccountName.blob.core.windows.net/ContainerName/DBName_20221107.bak'. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

The Account kind of storage account is "Storage (general purpose v1)".