SQLAzure - Restore a SQL Azure database from a .bacpac located in Azure Storage

To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.

{
  "Id": "6936e720-17a2-4a17-97d6-8f19ee040b01",
  "Name": "SQLAzure - Restore a SQL Azure database from a .bacpac located in Azure Storage",
  "Description": "Given an existing [.bacpac](https://msdn.microsoft.com/en-us/library/azure/hh335292.aspx) in Azure Storage this template restores a SQL Azure database.\n\n**Note** - The storage account used needs to reside in the the same subscription as the SQL Azure Server.",
  "Version": 3,
  "ExportedAt": "2015-04-01T11:36:52.686+00:00",
  "ActionType": "Octopus.Script",
  "Author": "bobjwalker",
  "Parameters": [
    {
      "Name": "AzureManagementCertificate",
      "Label": "The Azure management certificate associated with the subscription",
      "HelpText": "The Azure Management Certificate that can be sourced via https://manage.windowsazure.com/publishsettings",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "AzureSubscriptionId",
      "Label": "The Azure Subscription ID",
      "HelpText": "The Azure Subscription ID that can be sourced via the [Azure Portal](https://portal.azure.com) or https://manage.windowsazure.com/publishsettings",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "AzureSubscriptionName",
      "Label": "The Azure Subscription Name",
      "HelpText": "The Azure Subscription Name that can be sourced via the [Azure Portal](https://portal.azure.com) or https://manage.windowsazure.com/publishsettings",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "SqlAzureAdminUser",
      "Label": "The SQL Azure Server Admin User",
      "HelpText": "The SQL Azure Server Administrator User",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "SqlAzureAdminUserPassword",
      "Label": "The SQL Azure Admin Password",
      "HelpText": "The SQL Azure Server Administrator Password",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Name": "SQLAzureServerName",
      "Label": "The SQL Azure Server",
      "HelpText": "The name of the SQL Azure Server.  Just the server name e.g.  wyn4its2by",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "DatabaseName",
      "Label": "The SQL Azure Database used for the  bacpac restore",
      "HelpText": "The name of the SQL Azure Database to which you wish to restore the .bacpac file.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "SQLAzureDatabaseEdition",
      "Label": "The SQL Azure database edition to use",
      "HelpText": "The [SQL Azure Database Edition](https://msdn.microsoft.com/en-us/library/dn546725.aspx) to use for the restore",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText",
        "Octopus.SelectOptions": "Web|Web\nBusiness|Business"
      }
    },
    {
      "Name": "SQLAzureServiceObjective",
      "Label": "The SQL Azure SQL Service Objective to use",
      "HelpText": "The [SQL Azure Database Objective](https://msdn.microsoft.com/en-us/library/dn546721.aspx) for the database being restored.\n\nBasic, S0, S1, S2, P1, P2, or P3.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "Basic|Basic\nS0|Standard (S0)\nS1|Standard (S1)\nS2|Standard (S2)\nS3|Standard (S3)\nP1|Premium (P1)\nP2|Premium (P2)\nP3|Premium (P3)"
      }
    },
    {
      "Name": "AzureStorageAccountName",
      "Label": "The Azure Storage Account where the .bacpac file is located",
      "HelpText": "The Azure Storage Account where the .bacpac file is located",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "AzureStorageContainerName",
      "Label": "The container name of the bacpac file",
      "HelpText": "The container in Azure storage where the .bacpac file is located.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "BacPacFileName",
      "Label": "The name of the bacpac file to restore",
      "HelpText": "The name of the .bacpac file to restore.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptBody": "#Check for the PowerShell cmdlets\ntry{ \n    Import-Module Azure -ErrorAction Stop\n}catch{\n    \n    $azureServiceModulePath = \"C:\\Program Files (x86)\\Microsoft SDKs\\Azure\\PowerShell\\ServiceManagement\\Azure\\Azure.psd1\"\n    Write-Output \"Unable to find the module checking $azureServiceModulePath\" \n    \n    try{\n        Import-Module $azureServiceModulePath\n        \n    }\n    catch{\n        throw \"Windows Azure PowerShell not found! Please make sure to install them from http://www.windowsazure.com/en-us/downloads/#cmd-line-tools\" \n    }\n}\n\nfunction Set-TempAzureSubscription{\n    param(   \n        [Parameter(Mandatory=$true)][string] $subscriptionId,\n        [Parameter(Mandatory=$true)][string] $subscriptionName,\n        [Parameter(Mandatory=$true)][string] $managementCertificate\n    )\n\n    #Ensure no other subscriptions or accounts    \n    Get-AzureSubscription | ForEach-Object { \n        $id = $_.SubscriptionId \n        Write-Output \"Removing Subscription $id\"\n        Remove-AzureSubscription -SubscriptionId $id -Force\n    }\n\n    #Ensure there are no other \n    Get-AzureAccount | ForEach-Object { Remove-AzureAccount $_.ID -Force }\n   \n    [byte[]]$certificateData = [System.Convert]::FromBase64String($managementCertificate)\n    $certificate = [System.Security.Cryptography.X509Certificates.X509Certificate2] $certificateData\n\n    Set-AzureSubscription -Certificate $certificate -SubscriptionId $subscriptionId -SubscriptionName $subscriptionName\n    Select-AzureSubscription $subscriptionName\n\n    Write-Output \"Azure Subscription set Id: $subscriptionId Name: $subscriptionName \"\n\n    $subscription = Get-AzureSubscription -Current\n\n    return  $subscription\n}\n\n\nfunction Restore-SqlAzureDatabase{\n    Param(    \n    [Parameter(Mandatory=$true)][string]$databaseServerName,\n    [Parameter(Mandatory=$true)][string]$databaseName,\n    [Parameter(Mandatory=$true)][string]$edition,\n    [Parameter(Mandatory=$true)][string]$serviceObjectiveName, \n    [Parameter(Mandatory=$true)][string]$storageName,\n    [Parameter(Mandatory=$true)][string]$containerName,\n    [Parameter(Mandatory=$true)][string]$sqlAdminUser,                       \n    [Parameter(Mandatory=$true)][string]$sqlAdminPassword,\n    [Parameter(Mandatory=$true)][string]$bacpacFileName,    \n    [Parameter(Mandatory=$false)][bool]$errorIfDatabaseExists=$true\n    )\n\n    $subscription = Get-AzureSubscription -Current \n         \n    $storageKey = (Get-AzureStorageKey -StorageAccountName $storageName).Primary\n    $storageCtx = New-AzureStorageContext -storageaccountname $storageName -storageaccountkey $storageKey\n    $password = $sqlAdminPassword | ConvertTo-SecureString -asPlainText -Force                                                      \n    $sqlCred = New-Object System.Management.Automation.PSCredential($sqlAdminUser,$password)\n    $sqlCtx = New-AzureSqlDatabaseServerContext -ServerName $databaseServerName -Credential $sqlCred    \n  \n    $databases = Get-AzureSqlDatabase -ServerName $databaseServerName\n\n    #Check to see there is a database on the server\n    Foreach ($d in $databases){\n        if($d.Name -eq $databaseName){\n            $database = $d\n            break\n        }    \n    }\n\n    if ($database -eq $null) {\n        Write-Output \"The SQL Azure Database: $databaseName WAS NOT found on $databaseServerName\"  \n    }\n     \n    if($database -ne $null){\n        if ($errorIfDatabaseExists -eq $true) {\n            Write-Output \"A database named $databaseName already exists. If you wish to override this database set the -errorIfDatabaseExists parameter to false.\" \n            return;\n        } else {\n            #Delete the existing database.\n            Write-Output \"The SQL Azure Database: $databaseName WAS found on $databaseServerName\"  \n            Write-Output \"WARNING! Removing SQL Azure database: $databaseName\"\n            Remove-AzureSqlDatabase -ServerName $databaseServerName -DatabaseName $databaseName -Force\n        }\n    } \n\n    Write-Output \"Starting database import...\"\n    $importRequest = Start-AzureSqlDatabaseImport -SqlConnectionContext $sqlCtx -StorageContext $storageCtx -StorageContainerName $containerName -DatabaseName $databaseName -BlobName $bacpacFileName -Edition $edition\n    \n    Write-Output \"Database import request submitted.  Request Id: $($importRequest.RequestGuid)\"  \n\n    Write-Output \"Checking import status...\"\n    $status = Get-AzureSqlDatabaseImportExportStatus -Username $sqlAdminUser -Password $sqlAdminPassword -ServerName $databaseServerName -RequestId $importRequest.RequestGuid\n    Write-Output \"Status: $($status.Status)\"\n    \n    while($status.Status.StartsWith(\"Running\") -Or $status.Status.StartsWith(\"Pending\")){\n        Start-Sleep -s 10\n        Write-Output \"Checking import status...\"  \n        $status = Get-AzureSqlDatabaseImportExportStatus -Username $sqlAdminUser -Password $sqlAdminPassword -ServerName $databaseServerName -RequestId $importRequest.RequestGuid\n        Write-Output \"Status: $($status.Status)\" \n    }\n\n    Get-AzureSqlDatabaseImportExportStatus -Username $sqlAdminUser -Password $sqlAdminPassword -ServerName $databaseServerName -RequestId $importRequest.RequestGuid   \n\n    if ($status.Status -eq \"Completed\") {\n        Write-Output \"Updating database service objective...\"\n\n        #Get the service objective.\n        $serviceObjective = Get-AzureSqlDatabaseServiceObjective -ServerName $databaseServerName -ServiceObjectiveName $serviceObjectiveName\n        Set-AzureSqlDatabase -ConnectionContext $sqlCtx -DatabaseName $databaseName -Force -ServiceObjective $serviceObjective\n        \n        Write-Output \"Updated database service objective.\" \n    }\n\n    return $importRequest\n}\n\n\n#Set the Azure Subscription\n$subscription = Set-TempAzureSubscription -managementCertificate $AzureManagementCertificate -subscriptionId $AzureSubscriptionId -subscriptionName $AzureSubscriptionName \n\nWrite-Output \"=============================================================\"\nWrite-Output \"Using SQL Azure Server $SQLAzureServerName\"\nWrite-Output \"Using Azure Storage Account: $AzureStorageAccountName\"\nWrite-Output \"Using Azure Storeage Container: $AzureStorageContainerName\"\nWrite-Output \"Using bacpac file: $BacPacFileName\"\nWrite-Output \"=============================================================\"\n\nRestore-SqlAzureDatabase -databaseServerName $SQLAzureServerName `\n    -databaseName $databaseName `\n    -edition $SQLAzureDatabaseEdition `\n    -serviceObjectiveName $SQLAzureServiceObjective `\n    -storageName $AzureStorageAccountName `\n    -containerName $AzureStorageContainerName `\n    -sqlAdminUser $SqlAzureAdminUser `\n    -sqlAdminPassword $SqlAzureAdminUserPassword `\n    -bacpacFileName $BacPacFileName `\n    -errorIfDatabaseExists $false\n",
    "Octopus.Action.Script.Syntax": "PowerShell"
  },
  "Category": "SQL Server",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/sqlazure-restore-database-from-azure-storage.json",
  "Website": "/step-templates/6936e720-17a2-4a17-97d6-8f19ee040b01",
  "Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAD9QTFRFlZ+r3DAr6p+dy8/V4G9t////5efp9M7NrLS+wCYm8/T1vcPK1tnd10xK+fn6/PLyUU5O+eXk3+Hk7O3u7/DxS2XoPwAADb9JREFUeNrsnYl6nbgOgMEYDHghGN7/Wa8k70B6s3AOZD5o2umcSaf+0S4bUbX/kat6QB6QB+QBeUAekAfkAXlAHpAH5AF5QB6QB+QBeUAekAckXMv4XwBZVCPVnwcZlZSNXRrzp0HGTkqplrY1zfKHQboGMZwoGvVXQUbVy152QaPUu3XrJJCl6Xsp1/SBfbdunQJiZd/3zVqqmfprIEb1iLHRpLF5s279FsQ0iCH3etQ03R8CQYyq74/MwTbN3wGxQFGRRJTaJiVL815z/wXIIiviQEunq2lsNyZhvdfcfw6iCMPavl9H20jkgV8gP1F2NRRJmvEvgIA4gAS0B8xkpexEYWB3F0ijAyOxfwAkcsBvHQk53QWW71HwGm8PIhJHazIS98HYdUqBar1TJD8EYQOABGNe+w0J0dj3iuSHIOMw6PRHOyDpdhggE2XvDmLYAChsDh4MSPI1g92DWkGaosbbey0kARbOyFCaTCYgDemioQWp3D+O9EO4NGNCRpIFMKQzjlG9TyS/iOwoE64jjeaVwICOzjeoGfgue38QshPRMV57lhpVjbNemZTMK7X+gaQRSRgQzaz2JDX9CjRiDvWV+gMgRniSltWMMV0TSo1fcIEjEAKUa7k/CDiomkjaeeAU8JEmoRAOuoLp/hWidTJp9RBiipkF07our9fj/Lpmn51MeM2TnAx5gnp/cRZj6P2aD6BdWoBu1QUeiESwWoCu8a10OBfzHUFaATIxoFssfjIxUKbZiJobkg/ibFSNny2aM/pa4Lt0y4eoWwJkQP9S11NQNoOmw18Ic0qDDsIIg59TiC517aTDa5a7OBDPLDjRBMemmbgTCIhjEINbNVpHLXzozzxAhI4mg9ETv7i4DwhYiHa6JfA2T9F6dPltaDwgBQifwgG5ZOAMlpNAZlrShEpW8ykG/mgkCaMmX40LXwX3uUBR21wLgoYxoMOtc22agpJlGBM5AYF5pcFUwOkXXr8Ty2n7IxrWgze4sIo6WrvD4LNx6pc8QDtzHVA0uwGIcJ6otO4IQhahfZLCtqYjYiUwsOlqEMMp8S31w4MIHrUKv1PvnZlhsUJjF4NAWHQ5PCRUIoGA5XutEpMJsquPFjvzX6GcB2I0Ybg45wWDpi/Iz7K07QPiOfZQEwtls7gShCL6kGe6U4tBg8Bmk7syfSjRpF0glOVCEDT3Mp0KQZyV+cxeswKEjur1baGcuc8O66bQsM10C0Wa6jy4oG2E7gXkXeAxdOdhmLkMBPxWSLJyFj5vBKJLURAGJ58m0NKNcuLh01UgLLvXU87CWSEQVlDUSOHu/gQp2xgaTSAidRFISICjl83UiyVYl3/NIdHiKQZy73pNEIq4BqTNzZht2w8sCISjXWjnqYtcEZtLwTBM9c2Qci5I+ouDYs2sQMGPZxH+Y5kGiFIE6nskp4LwEPcmTpaBd99MqZTiLHPK2wwRDAQq5sxVjeS+enMBSGhAzMRhQsTIUOK1Lz9w2cWHZqy+YSevkMiknWvSMRfZoGg2mX1ecBA6yHupCyRCEqDkasaqMYsYc/LGRwWUmdHd7j4dG/x4ukIiE3HQ382KVDF546NAN9XHSmQsWo65wkbmuFSdxcdCtQ7yKP2ZgzLdx9dc19kSEbFqF0mzdsYuDgydf/I/RW8m324jPGUgPPgsoTPz0Af5MNn0p5ZgZpDJ9F6QfI2ztxQf/TT3DS+2J8Hm8b/sYAJxmXeCzJukikdnpcUUG5BeKKzQnfpf0UJUX4gmpyaNdVoQJlWzYSGGG9I5Fz0mXtoJGEh9sPc70ZZErBrN+0AMyyTCkkEwr1BJe1hOwnfysEiQyl5dMWneqlp8iGGCstyI4YLIVKT4gwfDJmvMTHDrIUP44FWz4JbEe93vnIUJXlSHyUDi92rnps1c+/LcgBiG7OIghqu6KHHXYxZlMsLLfpAzlAGTfjB0ICzlgLq0jqO5rGbnIAudtU+KqpAfKiI25XghCM3cuYlvn34+D2Qil5rqKDZlWRY/BA97CkM4aWRb89Pz2+eBsIHMedab1smks62fogs0+JMSDmL+3RH080B8a9qDCJMVvXrehgiu6yiP+pRN0epEgQi3SeUkkgeXXUOuDmdWBn7Wbuh5Gz2U67JtgsvqomUdtw4RQnNx3hMNJ269QS2iXRN7DrmUmXXGIYr+48knBqoTLUR4xztTXzRU73OgSPvSmov27OscELCEQWBgQM1hrjqc2tR+EPx1ojgVZMJTc+hzQzXl2sCc0pVMFkDRLa85iHbWyQe0Xoau1rkrg0AMk5VU5pJCmeXOILR9CMGCJ7cL5TuDJCVReDe7Aoi5K8hUUwKYc4A0MoXCLRy/+vHOIKBYPnXnbVk7BY1KS78zCKPNJShmY/9pjo0ToJjW/PErtJHxniCCjjtAxMBds9LXcrYCIZjFau4PAqURxwg+bDvvuJ/WdeiiEGW8PYge9GSEL7yjMNxOlLGd87XjGi3jriC4k4tHY8H5Gn94GUtc56QiCBn5eGcQMHRB9epEe2yDE0boe4y2i0f8jUcBkPV2IHg2nmHDkwk+uAqD573Q1dps0WAqYPTLi0L7r0CAAXs4NR3vxy8mi+fDAKRQI0AZ7wgyD7j8AQ/O0bMjrDFL8cjeYu0m+KEDux2IyLo4qFM0Q6R4GKnbgbQ3BDE6UdRsXpxWdblIrN00p0fiuBfIpCMGbtIafHwS8UAkYaHG2uLpRHBcKzqvW4GM6Skxhs62a6R7fh0fPgyZripARnK8NwOJ8gh9UXz00K0fn5p2v1uUXXZp771AhN6cc8PZLt4ejFJ+3INV8fm3cQkl7nqngOj9le7jJ8ARAwgqF0HFhxDHDq775Vp0SgGb/308XEEjg5KLbUgmo1Kdx8hSlRuBOHlU2bPfBp8GzSIGPn1o246e3BvBB9usKLwPCHPHqPAx42C1thAIkTQKn80fF6tsNtHiTiB0imelAQlBIluBOJmAVPBRXWXL6QM3ATGYslPhKpNEmq1AnJ04kI2vvQnIxAftXWofQRYUyGZxOJMDOXZjd+4BYnU6mZdApOw3AulwcAWR2O2ib9EOEoNOSSCqFi1f4ViXbL2Lokki3ka2MrkDiKryg5IIgqePRpxRozYUjmQxi9o+Pb1e3/tVVTG1yaJuGZz2IHt/nGoEN9zQbBe1di53NOCEi3p3vbwbX8oD7n1PkzfwH5RljX7iDs7fMDQ5yHrrtrmpLFeDyKraqDbpFk6pkRKsO04NckYBJW8a5bZCpWh9s7HrXpMzfhVEVdX2RtLENhpJJSWNcUKMkBqqppgTBmKBPGVEVeu68UIQ4NjPLwtjtUg08KOx2dCK3eQ2SOQtSAMkciHIUlX9/tMmkRQUXiB7JwtlbpbPXwBiqqra3cZVxUlnSaPCHwCLPzo/jYp1JUi/U6yuwZltNH6uPxh8YuXRHKcRdMsCSHsViK0KjzUqWSWMvt8bj5EHY3LR3MfWdt1yGUiVCQRFUdGXBNWqjklU6KhkOmUpD4Yqq1uvAmkAZHVdBZrXBhQ0CXcBDmcm2y4c+uHCnGxIVJZNlfVWkIpcVgf330HY0e19UIqyODMpyUGzlkwYWb4FkfFFtv7/QSwtP0CYTFCUxq877VpzgWASmWXAdtN7fCdIUKcyUEBo6StSKU9i8s6Q7Lyboiw4a9JhfL8KpE/j/3Lr7WMzyJHEiqTzAjEuoy+cs/Nc14CYqjoK62AxMnnbPqTAVC+iQHBQOUbFctnYUjFXSYQU6yD36vNAntTL0sCzhvL57d03arfP8GaJVJu/fu03xUnn1KtznSGXCO/vPVYmS3uljWx1q/eRJQ/mfr6sT+ibIy+LFZZpr/VayyZE7lPCzk2XpQmznwxffulova/FkUIk3VFxAiWIT+jlZwOL15eOcftSZK+KpR94MaNkVmF9MggQQ7y5EERVpXKBoZfeyNhYmXjVOjYRTFXaC0G8SIKb2lbvnYzlFU2PX7y977TotZr1FZDFk7ipnoWhLzJUJqBO1BmiXpYfxVyuGzdNzKUglMgHmWQRfWloSDmkYW6BaZwppryeJenYi8eBfqn50ESZNMFARuUyYhnbV2qbBVuXpjQuczdF+nhVO6j3JIszENO4MCkzmx59C3VbpvuWtrUvHr/+9QZdcMPGyUJu2gtyN4U5erV1wZHlLx7H/NWWaRNAKK3fh2572IaIFkNiMXcACb4LKI5KCih8q+PH7QxVV0v36pHlX99WMLLaBfmi8D2I5ytOlZYY6ZtXv2rhOztWNghlp1gdvpxgr1ApnR9f/qaFb+0hRqFsh6tjMNmJIo+J9uWvI/nm9vQaUfIb3JQG0imXz2fRsHn5C2K+e2DArH1QsNhvGKuUR462OWhsr/Llbyf4yaEaGR2Yu83gsVaftLgMUtqN4b/hFR4/O69lk1iUsVTTG+VFofbbz+YN73776VFAH99dG1Iu7l09Uh1bdCdf/wqlXxyXHRML5sD/GBD/jpfx/fJsvOttu589vnXv2KhAIBgYQQNfNg//hBdyQcio+vCjxxpks1gLApmqj+rjox0/5G1BgteVfbaPhTjR6Okwl/kAFtl/9PcGyWqpPutEYFW1dM5CAARkcneJlDwLlVP+dVDhMNdHW8mP45TzriBZ7k+Xi4W9kbMS0v5JkDdeD8gD8oA8IA/IA/KAPCAPyAPygDwgD8gD8oA8IA/IA/IXr/8JMAAhf0RDrOWy2QAAAABJRU5ErkJggg==",
  "$Meta": {
    "Type": "ActionTemplate"
  }
}

History