Powershell / Backup SQL Server DBs to Azure Storage with Powershell script

With this simple script, you can backup all databases to Azure daily, you can hardcode the parameters values or you can use task manager to pass them to the PS script. You need to have Azure PowerShell modules installed on your machine (how to).

First off all, let’s try just to connect to your Azure Storage Account
You can use azure credential-publishsettings file to do that, or putting the variables with access key, subscription and storage account name as in this example:

 

You should get something like this, of course, depends on containers/blobs names you have in Azure storage

 

Now let’s do DB instance connection

 

I will use PSCredential object. More about how to secure password with PowerShell you can read on the following post.

For this example I will use SQL login testuser account with password “testuserpassword”, and convert my password to secure string.

Now you can use secured credential to connect to any of your SQL server instance having “testuser” SQL login account

 

Depend on DB names on your instance you should get something like

 

Now we can combine those two scripts and try to create container on our Azure storage account and copy our DB backups there.

 

There are couple different commands to copy backups to Azure such as

 

Or for every blob each

 

 

Similar Posts:

2 Responses to “ “Powershell / Backup SQL Server DBs to Azure Storage with Powershell script”

  1. Praharsh Pandey says:

    Hi, I went through the script and see that this may be very useful for me. However, my database is around 900 GB. Is there any way I can use compression or use transactional backup.

    • Barac says:

      Hi Pandey,
      Regarding backup compression just add line
      $dbBackup.CompressionOption=1
      before db backup expression

      Example:
      $dbBackup.CompressionOption=1
      $dbBackup.Database=$dbname

      Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *