Generate SAS For Backup to Azure Storage

Posted: September 7, 2020 | Author: | Filed under: Uncategorized | Leave a comment


#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

view raw

Generate_SAS_For_BackuptoAzure.ps1

hosted with ❤ by GitHub

https://stuart-moore.com/creating-azure-blob-storage-account-for-sql-server-backup-and-restore-with-powershell/

Managed_backup function fn_is_master_switch_on() was missing

Posted: September 5, 2020 | Author: | Filed under: Uncategorized | Leave a comment

When I am working on managed-backup on SQL Server 2019 to backup to Azure.
I run into a situation that I cannot find a listed system scalar function:

fn_is_master_switch_on()

From MSDN:

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/managed-backup-fn-is-master-switch-on-transact-sql?view=sql-server-ver15
But it occasionally appears from intelligence menu context.

The function returns from this query on my instances:

SELECT name, type_desc
FROM msdb.sys.objects
WHERE SCHEMA_ID = SCHEMA_ID(‘managed_backup’)
and name=’fn_is_master_switch_on’

But cannot find it from a few machines.

The related stored procedure is working fine.

sp_backup_master_switch

After couple of hours, I can see the function again.

select msdb.managed_backup.fn_is_master_switch_on()

Custom Split With JSON and String_Agg function

Posted: September 4, 2020 | Author: | Filed under: Uncategorized | Leave a comment


–SQL Server 2017 or 2019
Create table test (Col1 varchar(900))
Insert into test values
('sql4 13434 22 test 39480'),
('sql2 DEF 39 tests'),
('data 123 258 256 tests 4587'),
('sql2 FED tests')
;with mycte as (
select Col1,[value],Cast([key] as int) k,
case
when lead([value]) over(partition by Col1 order by [key]) like'%test%'
and try_cast([value] as int) is not null
then 1
when [value] like'%test%'
and lag(try_cast([value] as int)) over(partition by Col1 order by [key]) is null
then 1
else 0 end newkey
from TEST
cross apply openjson('["'+(replace(Col1,' ','","')+'"]'))
)
,mycte2 as (
select Col1 ,k, [value]
, Sum(newkey)over(partition by Col1 order by k) grp
from mycte
)
,mycte3 as (
select Col1,grp, (case when grp=0 then string_agg([value],' ') WITHIN GROUP ( ORDER BY k) else null end) col1a
, (case when grp=1 then string_agg([value],' ') WITHIN GROUP ( ORDER BY k) else null end) col1b
from mycte2
group by Col1,grp
)
select Col1, max(col1a) col1a , max(col1b) col1b
from mycte3
group by Col1
order by 1,2
drop table test

view raw

Custom Split with JSON and String_AGG.sql

hosted with ❤ by GitHub

https://docs.microsoft.com/en-us/answers/questions/85586/how-to-split-a-string-into-two-different-fields-in.html