Musings of a Data professional

Stuart Moore

Creating Azure Blob storage account for SQL Server backup and restore with PowerShell

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

Previous

Creating Azure Blob storage account for SQL Server backup and restore via the portal

Next

Nottingham Global Azure Bootcamp 2019

1 Comment

  1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén