Azure Blob download using PowerShell

Erin Davis 1 Reputation point
2020-07-30T16:52:14.127+00:00

We receive weekly FULL and daily DIFF back ups from our hosted ERP quoting system provider. We are using PowerShell code and task scheduler to download the most recent file from a blob container to a local server location we use for our back ups. Once downloaded we have a SQL restore job that uses those downloaded files.

When I download the files from Azure Storage explorer manually and run the restore job it works fine. When I run the restore job from the Powershell downloaded file i get an error.

.bak' is incorrectly formed and can not be read.

Or the code will work perfectly for a week and then stop downloading properly. I receive that same error or it just gets sutck.

I cannot figure out why this is happening. Anyone run into this and fix it?

Is there a better way to get the files to download automatically and then restore? We need to always pull the most recent file for the week.

14711-powershell.jpg

Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,372 questions
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,488 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Sumarigo-MSFT 43,011 Reputation points Microsoft Employee
    2020-08-04T12:39:09.953+00:00

    @Erin Davis Firstly, apologies for the delay in responding here and any inconvenience this issue may have caused.

    For better understanding the issue : Are you using the same blob in task scheduler and PowerShell?

    • Are you using the differential backup instead of a full backup?
    • Can you share the screen shot of the error message?
    • Which version SQL Server are using?
    • Additional information: This error may also arise if the SQL Backup Agent service doesn't have the necessary permissions. For example, if you are restoring across a network, the SQL Backup Agent service must have read permission to access the folder containing the backups. For more information on the permissions required by the SQL Backup Agent service, check Permissions once

    (If the reply was helpful please don't forget to accept as answer, thank you)


  2. Erin Davis 1 Reputation point
    2020-08-06T17:05:12.587+00:00

    Hello,

    We are using task scheduler to run the PowerShell code for the blob. We use it to download the latest differential and/or full.. We are using PowerShell 7 for the code. We have code for the differential each day and then one for the full on Sunday.

    SQL Server is 2017.

    We download the back up from the blob container to a network drive and then run SQL to restore.

    When I download manually we never have an issue with the restore script.

    When we use the PowerShell it will run fine for a while and the restore will work but sometimes it will either finish the download and I get the attached error when I try to restore, or it will just get stuck and not finish.

    16139-image.png

    0 comments No comments

  3. Tonney Vincent 6 Reputation points
    2021-07-28T20:46:34.76+00:00

    @Erin Davis : I am looking to accomplish the same thing download files from Blob and restore it to SQL Server database , could you provide me this PS code @tonycesta2628@Stuff .com

    Also I am doing it from SQL on Azure VM , in this case what would be the smtp details.

    Appreciate this#