I know that I need to do something with a key but I'm unsure how to do this. RRS feed

  • Question

  • The code is copied from my power shell script, there is some comments to help understand what I've done at certain parts but I am new to this so it might not make sense. It is using azure resource cmdlets because other cmdlets are being phased out.

    I need to do a backup of the database created to a blob storage account
    I know that I need to do something with a key but I'm unsure how to do this.

    Thanks for any help you can give :)

    $azurename = "beaconinc"
    $azurepassword = ConvertTo-SecureString "Beacon-Inc105" -AsPlainText -Force
    $psCred = New-Object System.Management.Automation.PSCredential($azurename, $azurepassword)
    #$pscred= $azurename, $azurepassword
    #"The -Credential parameter cannot be used with Microsoft Accounts. It's somewhat subtle in the help, but it's technically documented."
    #Login-AzureRmAccount -Credential $psCred
    #List of variables names that makes writing the script easier
    $RGname = "BeaconIncResource"
    $StorageName = "beaconinc" 
    $StorageName2 ="beaconinc106"
    $Location = "UK West"
    $AppServicePlan = "beaconappservice"
    $webApp = "beaconwebapp106"
    $server = "beaconserver106"
    $database ="beacondatabase"
    # create variables  
    $storageAccount = "mystorageaccount"  
    $storageKey = "<storageaccesskeyvalue>"  
    $secureString = convertto-securestring $storageKey  -asplaintext -force  
    $credentialName = "mybackuptoURL" 
    #Creation of the resource group: using variables names to ease use
    New-AzureRmResourceGroup -Name $RGname -Location $Location
    #if else statement used to determine the availability of the storage name
    if(!(Get-AzureRmStorageAccountNameAvailability -Name $storageName))
            Write-Host "Trying to create storage account with name: $storageName"
            New-AzureRmStorageAccount -ResourceGroupName $RGname -AccountName $StorageName -Type "Standard_GRS" -Location $Location -EnableEncryptionService Blob -Kind "BlobStorage" -AccessTier Hot
        #if the storage account name is not available it will try to use the second name that 
        #includes numbers and therefore is more likely to be available
             Write-Host "Trying to create storage account with name: $storageName2"
            New-AzureRmStorageAccount -ResourceGroupName $RGname -AccountName $StorageName2 -Type "Standard_GRS" -Location $Location -EnableEncryptionService Blob -Kind "BlobStorage" -AccessTier Hot
        #if else statement contains comments so issues can be addressed
        #App service plan created, using the free service
        New-AzureRmAppServicePlan -Name $AppServicePlan -Location $Location -ResourceGroupName $RGname -Tier free -WorkerSize Large -NumberofWorkers 10
        #web app created that will be part of the app service plan, location remains UK west 
       New-AzureRmWebApp -Name $webApp -AppServicePlan $AppServicePlan -ResourceGroupName $RGname -Location $Location
       #Get-Help Get-Credential
        New-AzureRmSqlServer -ResourceGroupName $RGname -Location $location -ServerName $server -ServerVersion "12.0" -SqlAdministratorCredentials $psCred
        #Remove-AzureRmSqlServer -ResourceGroupName $RGname -ServerName $server
        #SQL database needs to be created that will be deployed to the new SQL server that has just been made
        New-AzureRmSqlDatabase -ResourceGroupName $RGname -ServerName $server -DatabaseName $database
        #Back up needs to be created for the databases 
        #The back up will be stored in a BLOB storage account
        $Key = (Get-AzureRmStorageAccountKey -ResourceGroupName $RGname -Name $StorageName2)[0].Value 
    # 3. Get Azure Storage Container Uri 
        $StorageUri = (Get-AzureRmStorageAccount -ResourceGroupName $RGname -Name $StorageName2 | Get-AzureStorageContainer | Where-Object { $_.Name -eq $AzureContainerName }).CloudBlobContainer.Uri.AbsoluteUri 
        $BackupUri = "$StorageUri/$BackupFileName" 
    # 4. Connect SqlServer cmdlet 
        Import-Module Sqlps -DisableNameChecking 3>$null 
    # 5. Start backup 
        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DatabaseName -Query "DROP CREDENTIAL myazure; CREATE CREDENTIAL MYAZURE WITH IDENTITY='$AzureStorageAccountName', SECRET='$Key'; BACKUP DATABASE $DatabaseName TO URL='$BackupUri' WITH CREDENTIAL='myazure';"  

    Saturday, April 22, 2017 4:45 PM

All replies

  • The script looks ok , 

    you mean the storage account key ?

    you can get by using this command : Get-AzureRmStorageAccountKey

    basically sql will write the data to the blob.

    here is the related article : https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url


    Sunday, April 23, 2017 2:21 PM
  • Thanks for your help Dinor and for the article, I'll check it out. Thank you!
    Sunday, April 23, 2017 9:09 PM