How to use Backup-SqlDatabase with SAS token?

Question

Im trying to implement log shipping using Backup-sqldatabase command, but SqlCredential is a required parameter for Azure Storage uploads according to the docs. However, im using a Shared Access Token credential, so i can't really use the SqlCredential parameter. If i do, this is the error i get:

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

Does this mean that SAS tokens/credential CANT be used for Backup-sqldatabase?

If i dont include the SqlCredential parameter, i get this error:

Backup-SqlDatabase : System.Data.SqlClient.SqlError: The file name  
"https://xxxxdev.blob.core.windows.net/DESKTOP-MCxxxx-MSSQL15.MSSQLSERVER-Test123-132767637897201085.bak" is invalid as a backup device name for the specified device type. Reissue the BACKUP statement with a valid file name and device type.  

Test123 is just a random test database i created on localhost for testing purposes

Also, is there a powershell script in place that performs logshipping completely with Azure Blob Storage integration? i have been searching and the only thing that is close to what im looking for is dbatools, but Invoke-DbaDbLogShipping requires a backupnetwork drive, so I think Azure Blob Storage cannot be used at this time unfortunately :/

Answer 1

Hi @Cataster ,

Does this mean that SAS tokens/credential CANT be used for Backup-sqldatabase?

No.
In this article, you can learn how we can fix the ‘WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature’ error and learn to back up the SQL database to Azure Blob storage using the SQL Server maintenance plan.

is there a powershell script in place that performs logshipping completely with Azure Blob Storage integration?

Azure Blob Storage isn't integrated into the log shipping wizards. You need to do your own backups to Azure Blob Storage, and then use the same certificate to restore the secondary server.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
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

Hello Cataster,

Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB"

This command creates a complete database backup of the database named 'MainDB' to the default backup location of the server instance 'Computer\Instance'. The backup file is named 'MainDB.bak'.

Based on your need do check the below link for the appropriate alternatives to try onto your script.

https://learn.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps#parameters

Check the below link for a better understanding of the subject

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15

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

Hope this answers all your queries, if not please do repost back.
If an Answer is helpful, please click "Accept Answer" and upvote it : )

Answer 3

@Seeya Xi-MSFT
I see, its unfortunate that Backup-SqlDatabase command doesnt support Shared Access Token at this time :/

Nonetheless, Ive created an Azure Access Key based SQL Credential.

I ran the command and got error as follows:

Backup-SqlDatabase : System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "https://xxxx.blob.core.windows.net/DESKTOP-Mxxxx-MSSQL15.MSSQLSERVER-testingblob-132768472127577686.bak:" Backup to URL  
received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request..  

According to the docs, this error happens because the storage account set to Blob King and has to be set to general purpose in order for it to work. However, my storage account is clearly set to general purpose Account Kind...

Ive tested it using T-SQL as well to isolate whether its a powershell issue or not, and I got the same error, so its not a powershell issue:

BACKUP DATABASE testingblob    
TO URL = 'https://xxxxx.blob.core.windows.net/logshippingtest/testingblob.bak'     
      WITH CREDENTIAL = 'AzureBackupBlobStore'     
     ,COMPRESSION    
     ,STATS = 5;    
GO  

Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file "https://xxxxx.blob.core.windows.net/logshippingtest/testingblob.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request..
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Answer 4

Hi @Cataster ,

You are trying to save a blob file using to non-blob storage or not using the Blob storage endpoint. To begin with, check your URL to see if you are pointing to the correct one.

This is a troubleshoot: Backup to URL fails with nonrecoverable I/O error 3271 in SQL Server.
Here is a doc about Create a storage account which you can refer to.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
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.