{
"metadata": {
"kernelspec": {
"name": "powershell",
"display_name": "PowerShell"
},
"language_info": {
"name": "powershell",
"codemirror_mode": "shell",
"mimetype": "text/x-sh",
"file_extension": ".ps1"
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"Backup Database Azure Blob Storage\n",
"==================================\n",
"\n",
"Description\n",
"-----------\n",
"\n",
"Notebook to walk through Azure Storage configuration, key generation, and scripting of a backup to blob storage command for on-premises SQL Server Database."
],
"metadata": {
"azdata_cell_guid": "6af59d69-ade7-480a-b33e-52a86fe5bfd3"
}
},
{
"cell_type": "markdown",
"source": [
"Steps of this procedure include:\r\n",
"1. Connect to Azure subscription\r\n",
"2. Provision resource group for SQL VM migration\r\n",
"3. Create a storage account\r\n",
"4. Create container for Blobs\r\n",
"5. Get Azure storage account key\r\n",
"6. Get Azure storage container Uri\r\n",
"7. Create Sql credentials on Sql server instances\r\n",
"8. Backup database on specific instance of Sql Server\r\n",
"9. Remove Sql credentials which were created on Sql server instances"
],
"metadata": {
"azdata_cell_guid": "b911ea4f-a3d8-4ac1-bff6-6c5eb1b514f9"
}
},
{
"cell_type": "code",
"source": [
"#Set Parameters\r\n",
"$Location = \"\" # Specify the valid Location such as 'West US 2','EASTUS' etc...\r\n",
"$ResourceGroup = \"\" # Resource group name(It should be of alphabets case insensitive)\r\n",
"$SkuName = \"\" # Specify the valid SKU...Such as 'Standard_LRS'\r\n",
"\r\n",
"$SqlPath = \"sqlserver:\\sql\\$($env:COMPUTERNAME)\" #This script will generate Sql Path"
],
"metadata": {
"azdata_cell_guid": "7ad525ec-4993-4e14-9677-4f77433b2123"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Sign in to Azure \r\n",
"Sign in to your Azure Subscription with the _Connect-AzAccount_ command and follow the on-screen directions."
],
"metadata": {
"azdata_cell_guid": "5dd9519d-3957-46ef-8988-440a043535b2"
}
},
{
"cell_type": "code",
"source": [
"Connect-AzAccount"
],
"metadata": {
"azdata_cell_guid": "18c920f2-a19a-49d6-9766-2d7539f6fe43"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Get Subscription\r\n",
"Below command will open a _**Dialouge Box**_ with list of subscriptions. Selecting one of those will set that Subscription for rest of the commands."
],
"metadata": {
"azdata_cell_guid": "cc6cb8c6-76b7-41a0-ab26-7713e72c2f7d"
}
},
{
"cell_type": "code",
"source": [
"$Subscription = Get-AzSubscription | Out-GridView -PassThru\r\n",
"Set-AzContext -SubscriptionName $Subscription"
],
"metadata": {
"azdata_cell_guid": "741c35fa-7923-4200-8c3a-497d62b4ae66"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"_If you don't know which Location you want to use, you can list the available Locations. Display the list of Locations by using the following code example and find the one you want to use. This example uses eastus. Store the Location in a variable and use the variable so you can change it in one place._"
],
"metadata": {
"azdata_cell_guid": "57de9eb7-a20b-4e9c-bf7b-ed6f2a838bee"
}
},
{
"cell_type": "code",
"source": [
"Get-AzLocation | select Location\r\n",
"$Location"
],
"metadata": {
"azdata_cell_guid": "6b0ea659-6dcd-48e1-a0d3-47b4a3ea9d66"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"\r\n",
"### Create a resource group\r\n",
"Create an Azure resource group with _New-AzResourceGroup_. A resource group is a logical container into which Azure resources are deployed and managed."
],
"metadata": {
"azdata_cell_guid": "455a6002-e5d5-4cb0-9146-8d417917c751"
}
},
{
"cell_type": "code",
"source": [
"# Create Azure resource group, if necessary\r\n",
"$ResourceGroup = Get-AzResourceGroup -Name $ResourceGroup\r\n",
"\r\n",
"if (!$ResourceGroup)\r\n",
"{\r\n",
" # Need to create a new resource group\r\n",
" Write-Output \"Resource Group $ResourceGroup does not exist. Creating...\"\r\n",
" $ResourceGroup = New-AzResourceGroup -Name $RG1 -Location $Location\r\n",
"}"
],
"metadata": {
"azdata_cell_guid": "d48948e1-3136-4e42-91e7-77a3301ae97d"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Create a storage account\r\n",
"Create a standard, general-purpose storage account with LRS replication by using _New-AzStorageAccount_. Next, get the storage account context that defines the storage account you want to use. When acting on a storage account, reference the context instead of repeatedly passing in the credentials. Use the following example to create a storage account called storageaccountazure with locally redundant storage (LRS) and blob encryption (enabled by default)."
],
"metadata": {
"azdata_cell_guid": "246d062f-e7d1-4f42-ba9f-153a12a286e4"
}
},
{
"cell_type": "code",
"source": [
"$StorageAccountName = \"storageaccountazure\"\r\n",
"$StorageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroup -Name $StorageAccountName -SkuName $SkuName -Location $Location\r\n",
"\r\n",
"$Ctx = $StorageAccount.Context"
],
"metadata": {
"azdata_cell_guid": "17e3421b-a80a-455c-bd6a-25daaff46514"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Create a container\r\n",
"Blobs are always uploaded into a container. You can organize groups of blobs like the way you organize your files on your computer in folders.\r\n",
"Set the container name, then create the container by using _New-AzStorageContainer_. Set the permissions to blob to allow public access of the files. The container name in this example is quickstartblobs."
],
"metadata": {
"azdata_cell_guid": "82503754-84ea-47b7-bcaf-31e5012da008"
}
},
{
"cell_type": "code",
"source": [
"$ContainerName = \"quickstartblobs\"\r\n",
"New-AzStorageContainer -Name $ContainerName -Context $Ctx -Permission blob"
],
"metadata": {
"azdata_cell_guid": "c4557040-5ff7-4f31-9243-01b850270b90",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Get Azure Storage Acount Key\r\n",
"This script will get the key for Storage Account which is been created."
],
"metadata": {
"azdata_cell_guid": "f48b23bf-01bc-48df-afed-ad3eea69c41d"
}
},
{
"cell_type": "code",
"source": [
"$StorageAccountKey = `\r\n",
" (Get-AzStorageAccountKey `\r\n",
" -ResourceGroupName $ResourceGroup `\r\n",
" -Name $StorageAccountName).Value[0]"
],
"metadata": {
"azdata_cell_guid": "e450bc4a-3373-4c7e-b0d0-f334712298b2"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Get Azure Storage Container Uri\r\n",
"The following script can be used to get the Uri of Storage container."
],
"metadata": {
"azdata_cell_guid": "5f7e616b-1e9f-4640-b61f-78d4327c5562"
}
},
{
"cell_type": "code",
"source": [
" $StorageUri = (Get-AzStorageAccount -ResourceGroupName $ResourceGroup -Name $StorageAccountName | Get-AzStorageContainer | Where-Object { $_.Name -eq $AzureContainerName }).CloudBlobContainer.Uri.AbsoluteUri "
],
"metadata": {
"azdata_cell_guid": "615d8e6e-2cbf-4001-8da0-1826185a06bf"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Create a SQL credential on all Instances of SQL Server \r\n",
"The following script can be used to create a generic SQL credential on all the Instances of SQL Server on a computer. If there's already an existing credential with the same name on one of the Instances of the computer, the script shows the error and continues."
],
"metadata": {
"azdata_cell_guid": "000a1320-fecf-4e0b-8d1f-00ab39fc44ce"
}
},
{
"cell_type": "code",
"source": [
"set-executionpolicy remotesigned\r\n",
"# load the sqlps module\r\n",
"import-module sqlps \r\n",
" \r\n",
"# set parameters\r\n",
"$SecureString = ConvertTo-SecureString $StorageAccountKey -AsPlainText -Force \r\n",
"$CredentialName = \"myCredential-$(Get-Random)\"\r\n",
"\r\n",
"Write-Host \"Generate credential: \" $CredentialName\r\n",
" \r\n",
"#cd to sql server and get Instances \r\n",
"cd $SqlPath\r\n",
"$Instances = Get-ChildItem\r\n",
"\r\n",
"#loop through Instances and create a SQL credential, output any errors\r\n",
"foreach ($Instance in $Instances) {\r\n",
" try {\r\n",
" $Path = \"$($SqlPath)\\$($Instance.DisplayName)\\credentials\"\r\n",
" New-SqlCredential -Name $CredentialName -Identity $StorageAccountName -Secret $SecureString -Path $Path -ea Stop | Out-Null\r\n",
" Write-Host \"...generated credential $($Path)\\$($CredentialName).\" }\r\n",
" catch { Write-Host $_.Exception.Message } }"
],
"metadata": {
"azdata_cell_guid": "f59b2416-a41f-43c0-85ea-1e113e6f4221",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Full backup for system Databases on a specific Instance of SQL Server \r\n",
"The full script can be used to back up the master and the msdb Databases on a named Instance of SQL Server. The same script can be used for any Instance by changing the Instance parameter value. SQL Server's default Instance is named DEFAULT."
],
"metadata": {
"azdata_cell_guid": "cca91ca6-bf10-4e32-9b4f-80133049c1b5"
}
},
{
"cell_type": "code",
"source": [
"set-executionpolicy remotesigned \r\n",
"import-module sqlps \r\n",
"\r\n",
"$InstanceName = \"DEFAULT\"\r\n",
"$BackupUrlContainer = \"https://$StorageAccountName.blob.core.windows.net/$ContainerName/\" \r\n",
"\r\n",
"Write-Host \"Backup Database: \" $InstanceName \" to \" $BackupUrlContainer\r\n",
" \r\n",
"cd \"$($SqlPath)\\$($InstanceName)\"\r\n",
"\r\n",
"#loop through Instance and backup specific Databases\r\n",
"$Databases = \"master\" \r\n",
"foreach ($Database in $Databases) {\r\n",
" try {\r\n",
" Write-Host \"...starting backup: \" $Database\r\n",
" Backup-SqlDatabase -Database $Database -BackupContainer $BackupUrlContainer -SqlCredential $CredentialName -Compression On\r\n",
" Write-Host \"...backup complete.\" }\r\n",
" catch { Write-Host $_.Exception.Message } }"
],
"metadata": {
"azdata_cell_guid": "9b21ab69-5be2-4d09-ac4b-3d43521a1188"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Remove a SQL credential from Instances of SQL Server \r\n",
"The following script can be used to remove a specific credential from all the Instances of SQL Server installed on the computer. If the credential does not exist on a specific Instance, an error message is displayed, and the script continues until all Instances are checked."
],
"metadata": {
"azdata_cell_guid": "63b41df3-6fe6-421e-9587-e5d65a0a5592"
}
},
{
"cell_type": "code",
"source": [
"set-executionpolicy remotesigned \r\n",
"import-module sqlps\r\n",
"\r\n",
"Write-Host \"Delete credential: \" $CredentialName\r\n",
"\r\n",
"cd $SqlPath\r\n",
"$Instances = Get-ChildItem\r\n",
"\r\n",
"#loop through Instances and delete a SQL credential\r\n",
"foreach ($Instance in $Instances) {\r\n",
" try {\r\n",
" $Path = \"$($SqlPath)\\$($Instance.DisplayName)\\credentials\\$($CredentialName)\"\r\n",
" Remove-SqlCredential -Path $Path -ea Stop | Out-Null\r\n",
" Write-Host \"...deleted credential $($Path).\" }\r\n",
" catch { Write-Host $_.Exception.Message } }"
],
"metadata": {
"azdata_cell_guid": "e195e874-6178-4f87-b5e8-94524cbc6ea1"
},
"outputs": [],
"execution_count": null
}
]
}