question

Cataster-7485 avatar image
0 Votes"
Cataster-7485 asked SadiqhAhmed-MSFT edited

How to use Backup-SqlDatabase with SAS token?

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 :/





sql-server-generalwindows-server-powershellazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @Cataster-7485,


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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

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://docs.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://docs.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 : )

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cataster-7485 avatar image
0 Votes"
Cataster-7485 answered

@SeeyaXi-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.

134553-image.png

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.



image.png (2.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft edited

Hi @Cataster-7485,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@SeeyaXi-msft
I was able to backup the database using the same URL using Shared Access Signature credential. The URL is certainly valid since that operation was successful...
As I had also mentioned my storage account kind is Storage V2 (general purpose) account kind, not blob, so my storage setup shouldn't be an issue either...

0 Votes 0 ·

Hi @Cataster-7485,

Sorry for misunderstanding your problem.
I've add a more related tag(Azure tag) for you.
Hope your problem could be solved as soon as possible!

Best regards,
Seeya

1 Vote 1 ·