Backup-SQLAzureDb.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
<#PSScriptInfo
 
.VERSION 0.2.0
 
.GUID 7a2d48ae-d404-4cd1-b526-3a268bf14aca
 
.AUTHOR mzaatar@outlook.com
 
.COMPANYNAME Mohamed Zaatar
 
.COPYRIGHT (c) 2017 Mohamed Zaatar. All rights reserved.
 
.TAGS Azure SQL Db backup runbook powershell
 
.LICENSEURI https://github.com/mzaatar/AzureScripts/blob/AddInitialScript/License.txt
 
.PROJECTURI https://github.com/mzaatar/AzureScripts
 
.ICONURI https://upload.wikimedia.org/wikipedia/commons/2/2f/PowerShell_5.0_icon.png
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
 
# .REQUIREDMODULES @({ModuleName="Azure", ModuleVersion="1.0.3"},{ModuleName="AzureRM.Profile", ModuleVersion="2.5.0"},{ModuleName="AzureRM.Sql", ModuleVersion="2.5.0"},{ModuleName="AzureRM.Resources", ModuleVersion="3.4.0"} )
 
.RELEASENOTES
0.1.0: - Add initial version
0.2.0: - Update Azure module dependencies
 
#>


<#
 
.SYNOPSIS
    This Azure Automation runbook automates the database backup in an Azure.
 
.DESCRIPTION
    This is a PowerShell runbook script.
    This runbook backup your SQL Azure database into an Azure storage account. This runbook can be scheduled through Azure to maintain your backup up to date daily/monthly/yearly.
 
    This runbook requires the "Azure", "AzureRM​.Profile", "AzureRM​.Sql" and "AzureRM.Resources" modules which are present by default in Azure Automation accounts.
    For detailed documentation and instructions, see:
 
    https://automys.com/library/asset/scheduled-virtual-machine-shutdown-startup-microsoft-azure
 
.PARAMETER AutomationConnection
    The name of the Azure Connection name asset in the Automation account that contains information required to connect to
    an external service or application from a runbook or DSC configuration.
    The user who will setup and use this connection must be configured as co-administrator and owner
    of the subscription for best functionality.
 
    By default, the runbook will use the credential with name "Default Automation Credential"
 
    For for details on credential configuration, see:
    http://azure.microsoft.com/blog/2014/08/27/azure-automation-authenticating-to-azure-using-azure-active-directory/
 
.PARAMETER SubscriptionName
    The name of Azure subscription in which the resources will be created.
 
.PARAMETER StorageAccount
   The name of the storage account where the database backup will be transfered to.
    
.PARAMETER BlobContainer
   The name of the storage blob container that will hold the backup files.
    
.PARAMETER StorageKey
   The storage key of the storage account where the database backup will be transfered to. It should have access to write and create blobs.
    
.PARAMETER StorageKeytype
   The Storage Key type of the storage account. By default it will use "StorageAccessKey" value.
    
.PARAMETER DbName
   The name of the database which will perform the backup on it.
    
.PARAMETER ResourceGroupName
   The name of the Resource Group of the database.
    
.PARAMETER ServerName
   The name of the Azure SQL Server where the database is.
    
.PARAMETER ServerAdmin
   The name of the Azure SQL Admin username.
 
.PARAMETER ServerPassword
   The password of the Azure SQL Admin account.
 
.EXAMPLE
    For be done later.
 
.INPUTS
    None.
 
.OUTPUTS
    Human-readable informational and error messages produced during the job. Not intended to be consumed by another runbook.
#>


param(
    [parameter(Mandatory=$true)]
    [String] $AutomationConnection,
    [parameter(Mandatory=$true)]
    [String] $SubscriptionName,
    [parameter(Mandatory=$true)]
    [String]$StorageAccount,
    [parameter(Mandatory=$true)]
    [String]$BlobContainer,
    [parameter(Mandatory=$true)]
    [String]$StorageKey,
    [parameter(Mandatory=$true)]
    [String]$StorageKeytype = "StorageAccessKey",
    [parameter(Mandatory=$true)]
    [String]$DbName,
    [parameter(Mandatory=$true)]
    [String]$ResourceGroupName,
    [parameter(Mandatory=$true)]
    [String]$ServerName,
    [parameter(Mandatory=$true)]
    [String]$serverAdmin,
    [parameter(Mandatory=$true)]
    [String]$ServerPassword
)

$VERSION = "0.2.0"
$currentTime = (Get-Date).ToUniversalTime()

Write-Output "Backup SQL Azure db automation script - version $VERSION"
Write-Output "Runbook started..."

# Main runbook content
try
{
    $securePassword = ConvertTo-SecureString -String $serverPassword -AsPlainText -Force
    $creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdmin, $securePassword

    # Generate a unique filename for the BACPAC
    $bacpacFilename = $ServerName + '-' + $DbName + '-' +(Get-Date).ToString("yyyyMMddTHHmmssZ") + ".bacpac"

    # Storage account info for the BACPAC
    $BaseStorageUri = "https://$storageAccount.blob.core.windows.net/$blobContainer/"
    $BacpacUri = $BaseStorageUri + $bacpacFilename


    Write-Output "Logging in to Azure..."
    # Get the connection
    $con = Get-AutomationConnection -Name $AutomationConnection
    $null = Add-AzureRmAccount -ServicePrincipal -TenantId $con.TenantId -ApplicationId $con.ApplicationId -CertificateThumbprint $con.CertificateThumbprint
    $null = Select-AzureRmSubscription -SubscriptionName $SubscriptionName

    Write-Output "Will backup db $DbName to $blobContainer blob storage in storage account $storageAccount with name $bacpacFilename ..."
    $exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
       -DatabaseName $DbName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
       -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

    # Check status of the export
    $status = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink

    Write-Output "Export status is:"
    $status
}
catch
{
    if (!$con)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}
finally
{
    "Runbook finished (Duration: $(("{0:hh\:mm\:ss}" -f ((Get-Date).ToUniversalTime() - $currentTime))))"
}