Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Setting up an Azure Blob container for SQL Server Backups and Restores
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
You can’t perform that action at this time.