How to use Backup-SqlDatabase with SAS token?

Cataster 641 Reputation points
2021-09-22T06:16:22.24+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,680 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,360 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,436 Reputation points
    2021-09-22T09:44:17.433+00:00

    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.

    0 comments
  2. Limitless Technology 39,351 Reputation points
    2021-09-22T14:19:57.303+00:00

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

    0 comments
  3. Cataster 641 Reputation points
    2021-09-23T05:22:01.547+00:00

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

    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.

    0 comments
  4. Seeya Xi-MSFT 16,436 Reputation points
    2021-09-23T07:13:03.293+00:00

    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.