In a previous post I looked at Creating Azure Blob storage account for SQL Server backup and restore via the portal. In this post I’m going to work through setting up the thing but using PowerShell. To me this is the simpler and easier way to do things. Most of it can be left running in the background while you drink coffee or read twitter, work smarter not harder 😉
Prequisites
For these examples I’m going to assume you’ve installed the up to date versions of these 2 PowerShell modules:
So no we’ll step through the PowerShell to setup the Azure Storage. I’ll post a screen shot after each block of code so you can see what you should expect to see. This is running on PowerShell 6.1.1 on MacOS (ain’t cross platform compatibility grand!)
TLDR; – I’ve uploaded the whole script to github in this gist – Setting up an Azure Blob container for SQL Server Backups and Restores
Setup
Import-Module dbatools
Import-Module Az
Connect-AzAccount
# Currently returns the wrong URL, use https://aka.ms/DeviceLogon instead
Subscription Check
Make sure you’re in the right subscription, here I’ve logged into my Visual Studio subscription and want to use my MVP Credits one:
#Check you're in the correct subscription
Get-AzContext
Get-AzContext -ListAvailable | Where-Object {$_.Name -Like 'MVP*'} | Set-AzContext
Get-AzContext
Check what’s there
#check existing Storage Accounts
Get-AzStorageAccount
Create the account
Here I’m using PowerShell splatting which is a great technique to avoid stupidly long parameter strings
I’m creating this storage account with the same settings as the one I created in the post Creating Azure Blob Storage for SQL Server Backups and Restores via the Azure Portal
#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
Getting the Access Keys
If you want to use the older Access Keys method to authenticate you’re pretty much done at this session. If you’re on a version of SQL Server that supports the Shared Access Signature method (2016 onwards), then it’s a good idea to move over to that method as it’s more secure and performs better
$AzStorageKeys = Get-AzStorageAccountKey -ResourceGroupName $NewStorageAccount.ResourceGroupName -Name $NewStorageAccount.AccountName
$AzStorageKeys
$Sql = "CREATE CREDENTIAL [$($NewStorageAccount.AccountName)] WITH IDENTITY='$($NewStorageAccount.AccountName)', SECRET = '$($AzStorageKeys[0].Value)'"
$Sql
Invoke-DbaSql -SqlInstance server1 -Database master -Query $Sql
Onwards to Shared Access Signatures
Grab a context so we can pass it in to other cmdlets:
$AzStorageContext = New-AzStorageContext -StorageAccountName $NewStorageAccount.AccountName -StorageAccountKey $AzStorageKeys[0].Value
Now we create a new container on the Storage Context
#Create a blob container called SQL
$AzStorageContainer = New-AzStorageContainer -Context $AzStorageContext -Name sql
Create a new Shared Access Policy to the context we’ve just created
#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
Now get the token for the Shared Access Policy, this is the actual ‘credential’ we need.
#Get the Shared Access Token
$AzSas = New-AzStorageContainerSASToken -Policy $SharedAccessPolicy.Policy -Context $AzStorageContext -Container sql
Grab the full URL to the sql container
#We need the URL to the blob storage container we've created:
$Url = $AzStorageContainer.CloudBlobContainer.uri.AbsoluteUri
And then a little bit of string manipulation to build our Query to create the credential
#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))'"
Invoke-DbaQuery -SqlInstance localhost -Database Master -Query $SasSql
And since we’re using dbatools to create the credentials, it’s very simple to do something like:
Invoke-DbaSqlQuery -SqlInstance server1,server2,server3 -Database Master -Query $SasSql
And then you have the credential on a number of SQL Server instances with no extra effort
The next blog in this short series will have some example of how you can then use these credentials to backup and restore via the dbatools commands
To save you piecing it togther I’ve uploaded the whole script to github in this gist – Setting up an Azure Blob container for SQL Server Backups and Restores
If you have any questions then please comment below or ping me on twitter
Eug
Hi Sturt,
Great module here. I have a question here .
We have an URI that was given to us by the cloud team which contains some backup files. the files are in the folder 04rt9 (I have modified the URI so it is not a real one.
$URI=”https://cbabc.blob.storage.azure.net/c04rt9?sv=2019-07-07&sr=c&sig=P7znfdsflm2Dxepc8fWfsdafsagdtretHmTwCQ2fdsffdsffsdfGanSCk%3D&st=2021-05-dfgertydfsgdsfg-05-19T09%3A52%3A59Z&sp=rwdl”
I issued this command
Restore-DbaDatabase -SqlInstance localhost -DestinationDataDirectory d:\Data -DestinationLogDirectory l:\log -NoRecovery -Path $URI
I got this error
Stop-Function : A positional parameter cannot be found that accepts argument ‘https://cbabc.blob.storage.azure.net/c04rt9?sv=2019-07-07&sr=c&sig=P7znfdsflm2Dxepc8fWfsdafsagdtretHmTwCQ2fdsffdsffsdfGanSCk%3D&st=2021-05-dfgertydfsgdsfg-05-19T09%3A52%3A59Z&sp=rwd)
should be a file, not a folder.
How do I pass down the folder structure
if I do a list I get this
zcopy.exe list $uri
INFO: dbatools-master.zip; Content Length: 25.28 MiB
INFO: t1_11.bak; Content Length: 392.50 KiB
INFO: t1_11.dif; Content Length: 38.00 KiB
INFO: t1_11.ldf; Content Length: 10.50 KiB
INFO: t1_12.ldf; Content Length: 5.00 KiB
INFO: t2_11.bak; Content Length: 390.00 KiB
INFO: t2_11.dif; Content Length: 150.00 KiB
INFO: t2_11.ldf; Content Length: 12.00 KiB
INFO: t2_12.ldf; Content Length: 5.00 KiB