Last active
September 8, 2020 17:12
-
-
Save jingyang-li/7e29df486efc49de8e5457f625a58d88 to your computer and use it in GitHub Desktop.
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 | |
#Create a new Container or use an existing one | |
$ContainerName= "devbackup" | |
#Create a new Resource Group or use an existing one | |
$ResourceGroupName = "rgdba" | |
#Create a new Storage Account or use an existing one | |
$AccountName = "backupacount" | |
# Choose data center | |
$Location = "centralus" | |
# Choose type | |
$SKUName = "Standard_GRS" | |
Connect-AzAccount | |
#WARNING: To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code EDL5EZVMX to authenticate. | |
#Check you're in the correct subscription | |
Get-AzResourceGroup -Name $ResourceGroupName -ErrorVariable notPresent -ErrorAction SilentlyContinue | |
if ($notPresent) | |
{ | |
# ResourceGroup doesn't exist | |
New-AzResourceGroup -Name $ResourceGroupName -Location $Location | |
} | |
# use splatting | |
$NewStorageAccount = @{ | |
ResourceGroupName = $ResourceGroupName | |
AccountName = $AccountName | |
Location = $Location | |
SKUName = $SKUName | |
Kind = "StorageV2" | |
AccessTier = "Hot" | |
EnableHttpsTrafficOnly = $true | |
} | |
$sa = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName | Where-Object StorageAccountName -eq $AccountName | |
if ($null -eq $sa) | |
{ | |
"Creating Storage Account $storageAcctName" | |
$sa = New-AzStorageAccount @NewStorageAccount | |
} | |
$StorageKeys = Get-AzStorageAccountKey -ResourceGroupName $ResourceGroupName -Name $AccountName | |
$StorageContext = New-AzStorageContext -StorageAccountName $AccountName -StorageAccountKey $StorageKeys[0].Value | |
#If ($null -eq (get-azstoragecontainer -context $AzStorageContext | Where-Object Name -eq $ContainerName) ) | |
#$sc = Get-AzStorageContainer -Context $StorageContext | Where-Object Name -eq $ContainerName | |
Get-AzStorageContainer -Context $StorageContext -Name $ContainerName -ErrorVariable notPresent -ErrorAction SilentlyContinue | |
if ($notPresent) | |
{ | |
# "Creating New Storage Container $ContainerName" | |
$sc = New-AzStorageContainer -Context $StorageContext -Name $ContainerName | |
} else { | |
$sc = Get-AzStorageContainer -Context $StorageContext -Name $ContainerName | |
} | |
#Create a Shared Access Policy giving (r)ead, (w)rite, (l)ist and (d)elete permissions for 10 year from now | |
$NewSharedAccessPolicy = @{ | |
Context = $StorageContext | |
Policy = $StorageContext.StorageAccountName+"sasPolicy10y" | |
Container = $ContainerName | |
ExpiryTime = (Get-Date).ToUniversalTime().AddYears(10) | |
Permission = "rwld" | |
} | |
Get-AzStorageContainerStoredAccessPolicy -Context $StorageContext -Container $ContainerName -ErrorVariable notPresent -ErrorAction SilentlyContinue | |
if ($notPresent) | |
{ # ResourceGroup doesn't exist | |
} | |
New-AzStorageContainerStoredAccessPolicy @NewSharedAccessPolicy -ErrorAction SilentlyContinue | |
#Get the Shared Access Token | |
$Sas = New-AzStorageContainerSASToken -Policy $NewSharedAccessPolicy.Policy -Context $StorageContext -Name $ContainerName | |
#We need the URL to the blob storage container we've created: | |
$ContainerUrl = $sc.CloudBlobContainer.uri.AbsoluteUri | |
$SasSql ="CREATE CREDENTIAL [$ContainerUrl] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET='$($Sas.SubString(1))' " | |
write-host $SasSql | |
# Invoke-DbaQuery -SqlInstance instance1,instance2 -Database Master -Query $SasSql |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
change instance names