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

vinixwu 41 Reputation points
2022-11-04T09:24:47.54+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,117 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,435 questions
{count} votes

6 answers

Sort by: Most helpful
  1. YufeiShao-msft 7,076 Reputation points
    2022-11-07T08:29:29.933+00:00

    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.

    1 person found this answer helpful.

  2. Preetha Rajesh 51 Reputation points
    2022-11-04T11:57:45.707+00:00

    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

    0 comments No comments

  3. Bjoern Peters 8,856 Reputation points
    2022-11-04T12:29:46.85+00:00

    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?)

    0 comments No comments

  4. Jon Gurgul 1 Reputation point Microsoft Employee
    2022-11-04T13:59:17.15+00:00

    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>/"  
    
    0 comments No comments

  5. vinixwu 41 Reputation points
    2022-11-07T02:26:58.567+00:00

    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)".

    0 comments No comments