Created
April 5, 2019 13:44
-
-
Save Stuart-Moore/624ab498817a5703f635ccc8b62561fc to your computer and use it in GitHub Desktop.
Setting up an Azure Blob container for SQL Server Backups and Restores
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Import-Module dbatools | |
Import-Module Az | |
Connect-AzAccount | |
# Currently returns the wrong URL, use https://aka.ms/DeviceLogon instead | |
#Check you're in the correct subscription | |
Get-AzContext | |
#check existing Storage Accounts | |
Get-AzStorageAccount | |
#we're going to use splatting | |
$NewStorageAccount = @{ | |
ResourceGroupName = "dbatools" | |
AccountName = "azblogdemo" | |
Location = "Uk South" | |
SKUName = "Standard_GRS" | |
Kind = "StorageV2" | |
AccessTier = "Hot" | |
EnableHttpsTrafficOnly = $true | |
} | |
#Create the Storage Account | |
New-AzStorageAccount @NewStorageAccount | |
#Get the Azure Storage Account keys | |
$AzStorageKeys = Get-AzStorageAccountKey -ResourceGroupName $NewStorageAccount.ResourceGroupName -Name $NewStorageAccount.AccountName | |
#We now have enough to create the SQL Credential use an Access key for authentication | |
$Sql = "CREATE CREDENTIAL [$($NewStorageAccount.AccountName)] WITH IDENTITY='$($NewStorageAccount.AccountName)', SECRET = '$($AzStorageKeys[0].Value)'" | |
Invoke-DbaSql -SqlInstance server1 -Database master -Query $Sql | |
#Create a Storage Context | |
$AzStorageContext = New-AzStorageContext -StorageAccountName $NewStorageAccount.AccountName -StorageAccountKey $AzStorageKeys[0].Value | |
#Create a blob container called SQL | |
$AzStorageContainer = New-AzStorageContainer -Context $AzStorageContext -Name sql | |
#Create a Shared Access Policy giving (r)ead, (w)rite, (l)ist and (d)elete permissions for 1 year from now | |
$SharedAccessPolicy = @{ | |
Context = $AzStorageContext | |
Policy = $AzStorageContext.StorageAccountName+"Policy2" | |
Container = "sql" | |
ExpiryTime = (Get-Date).ToUniversalTime().AddYears(1) | |
Permission = "rwld" | |
} | |
$AzSharedAccessPolicy = New-AzStorageContainerStoredAccessPolicy @SharedAccessPolicy | |
#Get the Shared Access Token | |
$AzSas = New-AzStorageContainerSASToken -Policy $SharedAccessPolicy.Policy -Context $AzStorageContext -Container sql | |
#We need the URL to the blob storage container we've created: | |
$Url = $AzStorageContainer.CloudBlobContainer.uri.AbsoluteUri | |
#The SASToken is prefixed with a '?' to make it easy to append to a HTTP querystring, but we don't need it, so use substring(1) to drop it | |
$AzSas | |
$SasSql = "CREATE CREDENTIAL [$Url] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET='$($AzSas.SubString(1))'" | |
$SasSql | |
Invoke-DbaSqlQuery -SqlInstance server1 -Database Master -Query $SasSql |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment