Azure database backup to blob using Powershell

Question

We need to backup the azure database and store it on blob so that it can be restored. I've seen this blog but it uses the third party cmdlets.

http://weblogs.thinktecture.com/cweyer/2011/01/automating-backup-of-a-sql-azure-database-to-azure-blob-storage-with-the-help-of-powershell-and-task-scheduler.html

Could someone please guide/help how above can be achieved using powershell.

score 0 · Answer 1 · edited May 21 '14 at 23:13

0

Backing up to WA Blob Store is not supported from Azure DB, rather the service does automatic backups for you with PITR capability. You'll find the following documentation useful:

http://msdn.microsoft.com/en-us/library/azure/hh852669.aspx

http://msdn.microsoft.com/en-us/library/azure/jj650016.aspx

Hope this helps.

edited May 21 '14 at 23:13

Graymatter

6,529
2
30
50

answered May 21 '14 at 22:48

Vijay Natarajan - Microsoft

1

score 0 · Answer 2 · answered Nov 20 '15 at 14:23

Here is my powershell script

https://gist.github.com/voxon2/be29a3fd6dabbb9155ca

Here is an article describing many different approaches other than powershell

http://blogs.msdn.com/b/mast/archive/2013/03/04/different-ways-to-backup-your-windows-azure-sql-database.aspx

score 0 · Answer 3 · edited May 23 '17 at 10:28

First get your Azure Automation Settings done (see here).

Edit the blow script and save it as .ps1 file. When you run it for the first time, it will ask you both your azure automation account and your database credentials. During this process, it will save your credentials in a local file securely (see here how it is done). After this time on wards, it uses the saved credentials.

The .psl file and the encrypted credential files should be stored in one directory

Once you are happy you can schedule it to run in task scheduler.

function Get-MyCredential
{
param(
$CredPath,
[switch]$Help
)
$HelpText = @"

    Get-MyCredential
    Usage:
    Get-MyCredential -CredPath `$CredPath

    If a credential is stored in $CredPath, it will be used.
    If no credential is found, Export-Credential will start and offer to
    Store a credential at the location specified.

"@
    if($Help -or (!($CredPath))){write-host $Helptext; Break}
    if (!(Test-Path -Path $CredPath -PathType Leaf)) {
        Export-Credential (Get-Credential) $CredPath
    }
    $cred = Import-Clixml $CredPath
    $cred.Password = $cred.Password | ConvertTo-SecureString
    $Credential = New-Object System.Management.Automation.PsCredential($cred.UserName, $cred.Password)
    Return $Credential
}


function Export-Credential($cred, $path) {
      $cred = $cred | Select-Object *
      $cred.password = $cred.Password | ConvertFrom-SecureString
      $cred | Export-Clixml $path
}

#Create a directory with you azure server name to isolate configurations
$FileRootPath = "C:\PowerShellScripts\AzureServerName"

Write-Host "Getting Azure credentials"
$AzureCred = Get-MyCredential ($FileRootPath + "AzureSyncred.txt")

#Use Azure Automation Account 
#(If You do not have it will not work with other accounts)
Add-AzureAccount -Credential $AzureCred
Select-AzureSubscription -SubscriptionId "myAzureSubscriptionId"

#DO NOT use tcp:myServerName.database.windows.net,1433 but only myServerName
$ServerName = "myServerName"
$Date = Get-Date -format "yyyy-MM-dd-HH-mm"
$DatabaseName = "myTargetDatabaseName"
$BlobName = $Date + "-" + $DatabaseName.bacpac"

$StorageName = "myStorageAccountName"
$ContainerName = "myContainerNameToStoreBacpacFiles"
$StorageKey = "myStorageAccountKey"

Write-Host "Getting database user credential"
#DO NOT use myDatabaseUsername@myServerName but only myDatabaseUsername
$credential = Get-MyCredential ($FileRootPath + "DbSyncred.xml")

Write-Host "Connecting to Azure database"
$SqlCtx = New-AzureSqlDatabaseServerContext -ServerName $ServerName -Credential $credential
Write-Host "Connecting to Blob storage"
$StorageCtx = New-AzureStorageContext -StorageAccountName $StorageName -StorageAccountKey $StorageKey
$Container = Get-AzureStorageContainer -Name $ContainerName -Context $StorageCtx
Write-Host "Exporting data to blob"
$exportRequest = Start-AzureSqlDatabaseExport -SqlConnectionContext $SqlCtx -StorageContainer $Container -DatabaseName $DatabaseName -BlobName $BlobName

Get-AzureSqlDatabaseImportExportStatus -Request $exportRequest 

# use the below script in powershell to execute the script
# powershell -ExecutionPolicy ByPass –File C:\PowerShellScripts\AzureServerName\mySavedScript.ps1 –noexit

Azure database backup to blob using Powershell

3 Answers3