April 19, 2018 at 11:45 am
Any chance you could post your run book code too? I have been trying to set up our retention policy and this sounds like a perfect fit.
April 19, 2018 at 12:24 pm
matt6288 - Thursday, April 19, 2018 11:45 AMAny chance you could post your run book code too? I have been trying to set up our retention policy and this sounds like a perfect fit.
Here you go. This is based on an Azure Automation account with a RunAsAccount and a few AutomationAccount variables. There is an Azure SQL Database we use to manged it all along with an Azure Key Vault. I think I have included all the relevant tables and stored procedures required for both dynamic retention and standard file retention. It's maybe a bit rough and ready as we are still developing as we go. I also think I have included all the relevant AzureDBA PowerShell functions. Anything missing, drop me a message and I will sort the missing bit for you.
Azure Runbook<#
.Synopsis
Returns a string containing an HTML table.
.Description
Returns a string containing an HTML table based upon a single data table passed in
.Parameter DataTableToConvert
A datatable for conversion to HTML table
.Parameter FixedFont
Defaulted to True and uses Courier New. If set to False, then Arial font is used
.Parameter TableBorder
Defaulted to True, this shows a border for the HTML table. When false, no border is shown.
.Example
AzureDBA-MakeHTMLTableFromDataTable -DataTableToConvert $Stats.Tables[1] -TableBorder $false
.Example
AzureDBA-MakeHTMLTableFromDataTable -DataTableToConvert $Stats.Tables[1] -FixedFont $false
#>
Function AzureDBA-MakeHTMLTableFromDataTable
{
param
(
[Parameter(Mandatory=$true)]
[System.Data.DataTable]$DataTableToConvert,
[Parameter(Mandatory=$false)]
[boolean]$FixedFont = $true,
[Parameter(Mandatory=$false)]
[boolean]$TableBorder = $true
)
if ($DataTableToConvert.Rows.Count -gt 0)
{
if ($FixedFont)
{
$HTMLTable = "<style type=`"text/css`"> .tg {font-family:`"Courier New`";font-size:12px}</style>"
}
else
{
$HTMLTable = "<style type=`"text/css`"> .tg {font-family:Arial;font-size:12px}</style>"
}
if ($TableBorder)
{
$HTMLTable = $HTMLTable + "<table border=`"1`" class=`"tg`">"
}
else
{
$HTMLTable = $HTMLTable + "<table border=`"0`" class=`"tg`">"
}
$Columns = $DataTableToConvert.Columns.ColumnName
foreach ($Column in $Columns)
{
$length = 0
foreach($DataRow in $DataTableToConvert.Rows)
{
$RowData = $DataRow.$Column | Out-String
if ($RowData.Length -gt $length)
{
$length = $RowData.Length
}
}
$length = $length * 10
$HTMLTable = $HTMLTable + "<col width = `"" + $length + "`">"
}
foreach ($Column in $Columns)
{
$HTMLTable = $HTMLTable + "<th>$Column</th>"
}
foreach($Stat in $DataTableToConvert.Rows)
{
$HTMLTable = $HTMLTable + "<tr>"
foreach ($Column in $Columns)
{
$TheData = $Stat.$Column
$HTMLTable = $HTMLTable + "<td>" + $TheData + "</td>"
}
$HTMLTable = $HTMLTable + "</tr>"
}
$HTMLTable = $HTMLTable + "</font></table>"
}
else
{
$HTMLTable = "No Records In DataSet To Show"
}
return $HTMLTable
}
<#
.Synopsis
Authenticates with Azure using AzureRunAsConnection
.Description
Authenticates to Azure using the AzureRunAsConnection credential. This is in active directory and is certificate based authentication to Azure Resources.
The AD object needs to have access to the relevant subscriptions to pull information back for Azure Resource commands.
.Parameter RunAsConnectionName
The name of the connection for azure authentication. This is optional and if not specified uses the default of AzureRunAsConnection (set up when automation account created)
.Example
$null = AzureDBA-RunAsAuthenticate -RunAsConnectionName
.Example
AzureDBA-RunAsAuthenticate
#>
Function AzureDBA-AzureAuthenticate
{
param
(
[Parameter(Mandatory=$false)]
[string] $RunAsConnectionName = "AzureRunAsConnection"
)
Disable-AzureRmDataCollection -WarningAction SilentlyContinue -InformationAction SilentlyContinue -ErrorAction SilentlyContinue
try
{
$connectionName = $RunAsConnectionName
$servicePrincipalConnection = Get-AutomationConnection -Name $connectionName -ErrorAction stop
$null = Add-AzureRmAccount -ServicePrincipal -TenantId $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint -ErrorAction Stop
}
catch
{
if (!$servicePrincipalConnection)
{
$ErrorMessage = "Connection $connectionName not found."
throw $ErrorMessage
}
else
{
Write-Error -Message $_.Exception
throw $_.Exception
}
}
}
<#
.Synopsis
Runs a SQL Command against an Azure SQL Database
.Description
Runs a SQL Command against an Azure SQL Database. A datatable object is returned for any results
Has a dependency on AzureDBA-GetKeyVaultSecretValue
.Parameter SQLCommandToRun
SQL Command to be ran
.Parameter ServerName
Name of the Azure SQL Server (Just server name only i.e. Don't include .database.windows.net)
.Parameter DatabaseName
Name of the Azure SQL Database
.Parameter $KVSubID
SubscriptionID of the Azure Key Vault which holds the database passwords.
.Parameter $KVName
Name of the Azure Key Vault which holds the database passwords.
.Parameter AzureServerSuffix
This is not required and defaults to $true which will add .database.windows.net onto the end of the server name supplied. Provide %false to this is you do not need this adding on the end
.Example
$MyDataTable = AzureDBA-RunSQLCommand -ServerName "MyAzureServer" -DatabaseName "MyAzureDatabase" -DBUserName "mydbuser" -DBPassword "securepassword" -SQLCommandToRun "SELECT * FROM dbo.MyDatabaseTable" -UseKeyVaultAuthentication $false -DBUserName "adbuser" -DBUserPWD "MySuperSecureDBPassword"
#>
FUNCTION AzureDBA-RunSQLCommand
{
param
(
[Parameter(Mandatory=$true)]
[string] $SQLCommandToRun,
[Parameter(Mandatory=$true)]
[string] $ServerName,
[Parameter(Mandatory=$true)]
[string] $DatabaseName,
[Parameter(Mandatory=$true)]
[string] $DBUserName,
[Parameter(Mandatory=$true)]
[string] $DBPassword,
[Parameter(Mandatory=$false)]
[boolean] $AzureServerSuffix = $true
)
Try
{
if ($AzureServerSuffix)
{
$SName = $ServerName + ".database.windows.net"
}
else
{
$SName = $ServerName
}
$DBConnection = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SName,1433;Database=$DatabaseName;User ID=$DBUserName;Password=$DBPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
$DBConnection.Open()
$DBCommand=new-object system.Data.SqlClient.SqlCommand($SQLCommandToRun, $DBConnection)
$DBCommand.CommandTimeout = 0
$DataResults=New-Object system.Data.SqlClient.SqlDataAdapter($DBCommand)
$DataTable=New-Object system.Data.DataSet
[void]$DataResults.fill($DataTable)
$DBConnection.Close()
return $DataTable
}
Catch
{
throw "Unable to run command against Server: $ServerName and Database: $DatabaseName. Check details and try again`n[$SQLCommandToRun]`n"
exit
}
}
<#
.Synopsis
Returns a storage context for a given subscription and storage account name
.Description
Returns a storage context for a given subscription and storage account name. If unable to set storage context then null is returned and should be handled accordingly
.Parameter SubscriptionID
SubscriptionID where the storage account resides
.Parameter StorageAccountName
Name of the storage account
.Example
$StorageContext = DBA-GetStorageContext -SubscriptionID "00000000-0000-0000-0000-000000000000" -StorageAccountName "mystorageaccount"
if (!StorageContext)
{
Write-Output "Unable to set storage context"
}
#>
FUNCTION AzureDBA-GenerateStorageContext
{
param
(
[Parameter(Mandatory=$true)]
[string]$SubscriptionID,
[Parameter(Mandatory=$true)]
[string]$StorageAccountName
)
$TryCounter = 0
$StorageContext = $null
WHILE ($TryCounter -lt 3 -and !$StorageContext)
{
$null = Set-AzureRmContext -SubscriptionID $SubscriptionID -WarningAction SilentlyContinue -InformationAction SilentlyContinue
$RG = Get-AzureRmResource -WarningAction SilentlyContinue -InformationAction SilentlyContinue | where-object {$_.ResourceType -eq "Microsoft.Storage/storageAccounts" -and $_.Name -eq $StorageAccountName}
$StorageKey = (Get-AzureRmStorageAccountKey -ResourceGroupName $RG.ResourceGroupName -AccountName $StorageAccountName).Value[0]
$StorageContext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageKey -ErrorAction Stop -InformationAction SilentlyContinue -WarningAction SilentlyContinue
$TryCounter = $TryCounter + 1
if (!$StorageContext)
{
Start-Sleep -s 30
}
}
Return $StorageContext
}
<#
.Synopsis
Checks for storage container and creates if specified to do do
.Description
Given a storage context and container name, checks for the existance of the container.
Optionally, if the container is not present, it is created
A boolean is returned to show existance of container
.Parameter SContext
A storage context for the storage account you want to check
.Parameter ContainerName
The name of the storage container
.Parameter CreateIfNotExist
Optional and defaulted to false. If set to true and the container does not exist, an attempt to create will be made.
.Example
$ContainerExists = AzureDBA-ContainerCheckCreate -SContext $StorageContext -ContainerName "mystoragecontainer"
.Example
$ContainerExists = AzureDBA-ContainerCheckCreate -SContext $StorageContext -ContainerName "mystoragecontainer" -CreateIfNotExist $true
#>
Function AzureDBA-ContainerCheckCreate
{
param
(
[Parameter(Mandatory=$true)]
[System.Object]$SContext,
[Parameter(Mandatory=$true)]
[string]$ContainerName,
[Parameter(Mandatory=$false)]
[boolean]$CreateIfNotExist = $false
)
$CheckMessage = $null
$ContainerCheck = Get-AzureStorageContainer -Context $SContext | WHERE {$_.Name -eq $ContainerName}
if(!$ContainerCheck -and $CreateIfNotExist)
{
$ContainerCreateError = $null
$null = New-AzureStorageContainer -Context $SContext -Name $ContainerName -ErrorAction SilentlyContinue -ErrorVariable ContainerCreateError
Start-Sleep -Seconds 15
$ContainerCheck = Get-AzureStorageContainer -Context $SContext | WHERE {$_.Name -eq $ContainerName}
}
if(!$ContainerCheck)
{
$CheckMessage = $false
}
else
{
$CheckMessage = $true
}
Return $CheckMessage
}
<#
.Synopsis
Checks for storage container for a specific blob file name
.Description
Given a storage context and container name, checks for the existance of a file.
Returns an object with the following
FileName
UTCTagFileName
SQLFileName (Any ' characters are replaced with '' to avoid any issues)
SQLUTCTagFileName (Any ' characters are replaced with '' to avoid any issues)
UTCLastModified
UTCLastModifiedTag
FileLength
FileExists (boolean)
.Parameter SContext
A storage context for the storage account you want to check
.Parameter ContainerName
The name of the storage container
.Parameter BlobFileName
Blob file name to check for
.RemoveFileIfExists
Boolean defaulted to false. If set to true and the file exists the file will be removed.
.Example
$ContainerExists = AzureDBA-StorageCheckFileExists -SContext $StorageContext -ContainerName "mystoragecontainer" -BlobFileName "myfile.pdf"
#>
function AzureDBA-StorageCheckFileExists
{
param
(
[Parameter(Mandatory=$true)]
[System.Object]$SContext,
[Parameter(Mandatory=$true)]
[string]$ContainerName,
[Parameter(Mandatory=$false)]
[string]$BlobFileName,
[Parameter(Mandatory=$false)]
[boolean]$RemoveFileIfExists = $false
)
$FileDetails = @()
$Details = New-Object System.Object
$FileNotInAzureStorage = $null
$UTCPrefix = "<UTCTAG>"
if ($BlobFileName -like "http*://*")
{
$BlobPrefix = $SContext.BlobEndPoint + $ContainerName + "/"
$BlobFileName = $BlobFileName -replace($BlobPrefix,"")
}
$GetFile = Get-AzureStorageBlob -Context $SContext -Container $ContainerName -Blob $BlobFileName -ErrorVariable FileNotInAzureStorage -ErrorAction SilentlyContinue
if($FileNotInAzureStorage)
{
$Error.Clear()
$Details | Add-Member "FileName" $null
$Details | Add-Member "UTCTagFileName" $null
$Details | Add-Member "SQLFileName" $null
$Details | Add-Member "SQLUTCTagFileName" $null
$Details | Add-Member "UTCLastModified" $null
$Details | Add-Member "UTCLastModifiedTag" $null
$Details | Add-Member "LastModified" $null
$Details | Add-Member "FileLength" -1
$Details | Add-Member "FileExists" $false
$Details | Add-Member "IsFileUTCTagged" $false
}
else
{
if ($RemoveFileIfExists)
{
$null = Get-AzureStorageBlob -Blob $BlobFileName -Container $ContainerName -Context $SContext | Remove-AzureStorageBlob
$Details | Add-Member "FileName" "****FileRemoved****"
$Details | Add-Member "UTCTagFileName" $null
$Details | Add-Member "SQLFileName" $null
$Details | Add-Member "SQLUTCTagFileName" $null
$Details | Add-Member "UTCLastModified" $null
$Details | Add-Member "UTCLastModifiedTag" $null
$Details | Add-Member "LastModified" $null
$Details | Add-Member "FileLength" -1
$Details | Add-Member "FileExists" $false
$Details | Add-Member "IsFileUTCTagged" $false
}
else
{
$UTCLastModified = $GetFile.LastModified.UtcDateTime
if (!$UTCLastModified)
{
$UTCLastModified = "1900-01-01 00:00:00"
}
$UTCLastModified = Get-Date $UTCLastModified -Format "yyyy-MM-dd HH:mm:ss"
$LastModified = $GetFile.LastModified.DateTime
if(!$LastModified)
{
$LastModified = "1900-01-01 00:00:00"
}
$LastModified = Get-Date $LastModified -Format "yyyy-MM-dd HH:mm:ss"
$UTCTag = $UTCPrefix + $UTCLastModified.ToString() + $UTCPrefix
if ($GetFile.Name -notlike "*$UTCPrefix")
{
$UTCTaggedFileName = $GetFile.Name + $UTCPrefix + $UTCLastModified.ToString() + $UTCPrefix
$IsFileUTCTagged = $false
}
else
{
$UTCTaggedFileName = $GetFile.Name
$IsFileUTCTagged = $true
}
$SQLFileName = $GetFile.Name -replace "'","''"
$SQLUTCFileName = $UTCTaggedFileName -replace "'","''"
$Details | Add-Member "FileName" $GetFile.Name
$Details | Add-Member "UTCTagFileName" $UTCTaggedFileName
$Details | Add-Member "SQLFileName" $SQLFileName
$Details | Add-Member "SQLUTCTagFileName" $SQLUTCFileName
$Details | Add-Member "UTCLastModified" $UTCLastModified
$Details | Add-Member "UTCLastModifiedTag" $UTCTag
$Details | Add-Member "LastModified" $LastModified
$Details | Add-Member "FileLength" $GetFile.Length
$Details | Add-Member "FileExists" $true
$Details | Add-Member "IsFileUTCTagged" $IsFileUTCTagged
}
}
$FileDetails = $Details
Return $FileDetails
}
<#
.Synopsis
Sends an email
.Description
Sends email using a predefined sendgrid account.
Details stored in the variables section within the automation account required are:
dba-sendgrid
sendgrid-smtp-server
sendgrid-smtp-port
dba-from
.Parameter Subject
Subject line of the email
.Parameter Body
Body of the email, can be empty
.Parameter Importance
Sets the email priority. Can be Normal, High or Low. If not specified or anything other than allowable values, Normal is used.
.Parameter To
A list of people to send the email to. Email addresses should be separated by ; (semi-colon)
.Parameter IsHTMLBody
Defaults to False if not supplied. Supply True if sending formatted HTML in the Body of the email
.Example
DBA-SendEmail -Subject "This is a test email"
.Example
DBA-SendEmail -Subject "This is another Test Email" -Body "There is more I need to tell you" -To "a.other@mail.com"
#>
FUNCTION AzureDBA-SendGridSendEmail
{
param
(
[Parameter(Mandatory=$true)]
[string] $Subject,
[Parameter(Mandatory=$true)]
[string] $To,
[Parameter(Mandatory=$false)]
[string] $Body = "<No Content>",
[Parameter(Mandatory=$false)]
[string] $Importance = "Normal",
[Parameter(Mandatory=$false)]
[boolean] $IsHTMLBody = $false
)
try
{
$credential = Get-AutomationPSCredential -Name 'dba-sendgrid'
$SMTPServer = Get-AutomationVariable -Name 'sendgrid-smtp-server'
$SMTPPort = Get-AutomationVariable -Name 'sendgrid-smtp-port'
$EmailFrom = Get-AutomationVariable -Name 'dba-from'
if ($Importance -notin "Normal","High","Low")
{
$Importance = "Normal"
}
$ReceipientListing = $To -split ";"
foreach ($receipient in $ReceipientListing)
{
if ($IsHTMLBody)
{
Send-MailMessage -smtpServer $SMTPServer -Credential $credential -Usessl -Port $SMTPPort -from $EmailFrom -to $receipient -subject $Subject -Body $Body -BodyAsHtml -Priority $Importance
}
else
{
Send-MailMessage -smtpServer $SMTPServer -Credential $credential -Usessl -Port $SMTPPort -from $EmailFrom -to $receipient -subject $Subject -Body $Body -Priority $Importance
}
}
}
catch
{
throw "Unable to send email. Check Automation Variables exist and are correct"
}
}
#Authenticate with Azure
AzureDBA-AzureAuthenticate
#Get variables required
$KVSubID = Get-AutomationVariable -Name 'KVSubID'
$KVName = Get-AutomationVariable -Name 'KVName-1'
$DBAServer = Get-AutomationVariable -Name 'AzureServer'
$DBADatabase = Get-AutomationVariable -Name 'AzureDatabase'
$DBAUserName = "automateddba-" + $DBAServer + "-" + $DBADatabase
$DBAPassword = AzureDBA-GetKeyVaultSecretValue `
-SecretName $DBAUserName `
-KVSubID $KVSubID `
-KVName $KVName `
-ReturnSecureString $false
$ContainerRetention = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "EXEC dbo.PRC_ListActiveContainerFileRetentions @ListType = 'Standard'"
#STANDARD FILE RETENTION
foreach ($CheckContainer in $ContainerRetention.Tables[0])
{
$R_ContainerFileRetentionID = $CheckContainer.ContainerFileRetentionID
$R_ContainerName = $CheckContainer.ContainerName
$R_Prefix = $CheckContainer.Prefix
$R_RetentionDays = $CheckContainer.FilesRetentionPeriod
$R_StorageAccountName = $CheckContainer.StorageAccountName
$R_StorageSubscriptionID = $CheckContainer.StorageAccountSubscriptionID
$R_NewFileHours = $CheckContainer.NoNewFileWithinHoursAlert
$Storagecontext = AzureDBA-GenerateStorageContext -StorageAccountName $R_StorageAccountName -SubscriptionID $R_StorageSubscriptionID
Write-Output "Looking for files older than $R_RetentionDays days in [$R_StorageAccountName] Container [$R_ContainerName] with Prefix [$R_Prefix]"
if ($R_Prefix -eq "*")
{
$R_Prefix = ""
}
$ContainerExists = AzureDBA-ContainerCheckCreate -SContext $StorageContext -ContainerName $R_ContainerName -CreateIfNotExist $false -ErrorAction Stop
if (!$ContainerExists)
{
Write-Warning "Container Does Not Exist [$R_ContainerName] in storage account [$R_StorageAccountName]"
$FilesPastRetention = $null
}
else
{
$FilesPastRetention = Get-AzureStorageBlob -Container $R_ContainerName -Context $StorageContext -Prefix "$R_Prefix" | WHERE {([DateTimeOffSet]::UtcNow - [DateTimeOffSet]$_.LastModified).Days -ge $R_RetentionDays} -ErrorAction Stop
foreach ($File in $FilesPastRetention)
{
$FileName = $File.Name
$RemoveStatus = AzureDBA-StorageCheckFileExists `
-BlobFileName "$FileName" `
-ContainerName $R_ContainerName `
-RemoveFileIfExists $true `
-SContext $Storagecontext
if ($RemoveStatus.FileName -eq "****FileRemoved****")
{
$null = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "EXEC dbo.PRC_LogContainerFileRetentionFileRemoval @ContainerFileRetentionID = $R_ContainerFileRetentionID , @BlobFileName = N'$FileName'"
}
Write-Output "Past Retention : $FileName"
}
if ($R_NewFileHours -gt 0)
{
Write-Output "Looking for new files in [$R_StorageAccountName] Container [$R_ContainerName] with Prefix [$R_Prefix] within last [$R_NewFileHours hours]"
$FileListing = Get-AzureStorageBlob -Container $R_ContainerName -Context $StorageContext -Prefix "$R_Prefix" | Where-Object { $_.LastModified -gt (Get-Date).AddHours(-$R_NewFileHours) } -ErrorAction Stop
if($FileListing.Count -eq 0)
{
AzureDBA-SendGridSendEmail -Subject "WARNING! CHECK! No File In Container [$R_ContainerName] In Last [$R_NewFileHours hours]" -To "sql2005@clarity.co.uk"
}
}
}
}
# DYNAMIC FILE RETENTION
$ContainerRetention = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "EXEC dbo.PRC_ListActiveContainerFileRetentions @ListType = 'Dynamic'"
foreach ($CheckContainer in $ContainerRetention.Tables[0])
{
$R_ContainerFileRetentionID = $CheckContainer.ContainerFileRetentionID
$R_ContainerName = $CheckContainer.ContainerName
$R_Prefix = $CheckContainer.Prefix
$R_DynamicRetentionPolicyID = $CheckContainer.DynamicRetentionPolicyID
$R_StorageAccountName = $CheckContainer.StorageAccountName
$R_StorageSubscriptionID = $CheckContainer.StorageAccountSubscriptionID
$R_NewFileHours = $CheckContainer.NoNewFileWithinHoursAlert
$Storagecontext = AzureDBA-GenerateStorageContext -StorageAccountName $R_StorageAccountName -SubscriptionID $R_StorageSubscriptionID
Write-Output "Looking for files to removed based on Dynamic Retention Policy [$R_DynamicRetentionPolicyID] in [$R_StorageAccountName] Container [$R_ContainerName] with Prefix [$R_Prefix]"
if ($R_Prefix -eq "*")
{
$R_Prefix = ""
}
$ContainerExists = AzureDBA-ContainerCheckCreate -SContext $StorageContext -ContainerName $R_ContainerName -CreateIfNotExist $false -ErrorAction Stop
if (!$ContainerExists)
{
Write-Warning "Container Does Not Exist [$R_ContainerName] in storage account [$R_StorageAccountName]"
$FilesPastRetention = $null
}
else
{
$FileListing = Get-AzureStorageBlob -Container $R_ContainerName -Context $StorageContext -Prefix "$R_Prefix" -ErrorAction Stop
$null = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "TRUNCATE TABLE dbo.TMP_DynamicRetentionFileWorker"
foreach ($File in $FileListing)
{
$FileName = $File.Name
$FileDate = $File.LastModified
$null = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "INSERT INTO TMP_DynamicRetentionFileWorker (FileURI,FileDate) VALUES ('$FileName','$FileDate')"
}
$FilesToDelete = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "EXEC PRC_DynamicRetention @DynamicRetentionPolicyID = $R_DynamicRetentionPolicyID"
foreach ($FileToDelete in $FilesToDelete.Tables[0])
{
$FileName = $FileToDelete.FileURI
$RemoveStatus = AzureDBA-StorageCheckFileExists `
-BlobFileName $FileName `
-ContainerName $R_ContainerName `
-RemoveFileIfExists $true `
-SContext $Storagecontext
if ($RemoveStatus.FileName -eq "****FileRemoved****")
{
$null = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "EXEC dbo.PRC_LogContainerFileRetentionFileRemoval @ContainerFileRetentionID = $R_ContainerFileRetentionID , @BlobFileName = N'$FileName'"
}
}
#Send Alerts If No New File Has Been Submitted To A Specified Storage Account Container Within X Hours
if ($R_NewFileHours -gt 0)
{
Write-Output "Looking for new files in [$R_StorageAccountName] Container [$R_ContainerName] with Prefix [$R_Prefix] within last [$R_NewFileHours hours]"
$FileListing = Get-AzureStorageBlob -Container $R_ContainerName -Context $StorageContext -Prefix "$R_Prefix" | Where-Object { $_.LastModified -gt (Get-Date).AddHours(-$R_NewFileHours) } -ErrorAction Stop
if($FileListing.Count -eq 0)
{
AzureDBA-SendGridSendEmail -Subject "WARNING! CHECK! No File In Container [$R_ContainerName] In Last [$R_NewFileHours hours]" -To "sql2005@clarity.co.uk"
}
}
}
}
$RPTDetails = AzureDBA-RunSQLCommand `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-ServerName $DBAServer `
-SQLCommandToRun "EXEC dbo.RPT_ContainerFileRetentionRemovals"
if ($RPTDetails.Tables[0].Rows.Count -gt 0)
{
$Body = "Container File Removals (Past Retention) [Last 24 hours] [Dates and Times are UTC] <br>"
$HTMLTable = AzureDBA-MakeHTMLTableFromDataTable -DataTableToConvert $RPTDetails.Tables[0]
$Body = $Body + $HTMLTable
AzureDBA-SendGridSendEmail -Body $Body -IsHTMLBody $true -Subject "Container File Removals (Past Retention) [Last 24 hours]" -To "sql2005@clarity.co.uk"
}
#MANAGED STORAGE ACCOUNTS - ANY NEW CONTAINERS WILL BE ADDED HERE
$ManagedStorageAccounts = AzureDBA-RunSQLCommand `
-ServerName $DBAServer `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-SQLCommandToRun "SELECT cmsar.StorageAccountName, cmsar.StorageAccountSubscriptionID FROM dbo.CFG_ManagedStorageAccountRetention AS cmsar"
foreach($ManagedStorageAccount in $ManagedStorageAccounts.Tables[0])
{
$StorageAccount = $ManagedStorageAccount.StorageAccountName
$StorageSubID = $ManagedStorageAccount.StorageAccountSubscriptionID
$StorageContext = AzureDBA-GenerateStorageContext -SubscriptionID $StorageSubID -StorageAccountName $StorageAccount
$Containers = (Get-AzureStorageContainer -Context $StorageContext).Name
foreach($Container in $Containers)
{
$null = AzureDBA-RunSQLCommand `
-ServerName $DBAServer `
-DatabaseName $DBADatabase `
-DBUserName $DBAUserName `
-DBPassword $DBAPassword `
-SQLCommandToRun "EXEC dbo.PRC_CheckAndAddContainerForManagedStorageAccountRetention @StorageAccountName = N'$StorageAccount', @StorageAccountSubscriptionID = '$StorageSubID', @ContainerName = N'$Container'"
}
}
DB Tables and Stored Procedures
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_ManagedStorageAccountRetention]') AND type IN (N'U'))
BEGIN
CREATE TABLE [dbo].[CFG_ManagedStorageAccountRetention](
[StorageAccountName] [NVARCHAR](100) NOT NULL,
[StorageAccountSubscriptionID] [UNIQUEIDENTIFIER] NOT NULL,
CONSTRAINT [PK_CFG_ManagedStorageAccountRetention] PRIMARY KEY CLUSTERED
(
[StorageAccountName] ASC,
[StorageAccountSubscriptionID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_DynamicRetentionPolicies]') AND type IN (N'U'))
BEGIN
CREATE TABLE [dbo].[CFG_DynamicRetentionPolicies](
[DynamicRetentionPolicyID] [INT] NOT NULL,
[DRPStep] [INT] NOT NULL,
[CutOffStart] [INT] NOT NULL,
[CutOffEnd] [INT] NULL,
[RetentionAction] [NVARCHAR](15) NOT NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_CFG_DynamicRetentionPolicies_RetentionAction]') AND parent_object_id = OBJECT_ID(N'[dbo].[CFG_DynamicRetentionPolicies]'))
ALTER TABLE [dbo].[CFG_DynamicRetentionPolicies] WITH CHECK ADD CONSTRAINT [CHK_CFG_DynamicRetentionPolicies_RetentionAction] CHECK (([CFG_DynamicRetentionPolicies].[RetentionAction]='Delete' OR [CFG_DynamicRetentionPolicies].[RetentionAction]='Archive' OR [CFG_DynamicRetentionPolicies].[RetentionAction]='Annual' OR [CFG_DynamicRetentionPolicies].[RetentionAction]='Month' OR [CFG_DynamicRetentionPolicies].[RetentionAction]='Week'))
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_CFG_DynamicRetentionPolicies_RetentionAction]') AND parent_object_id = OBJECT_ID(N'[dbo].[CFG_DynamicRetentionPolicies]'))
ALTER TABLE [dbo].[CFG_DynamicRetentionPolicies] CHECK CONSTRAINT [CHK_CFG_DynamicRetentionPolicies_RetentionAction]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_ContainerFileRetention]') AND type IN (N'U'))
BEGIN
CREATE TABLE [dbo].[CFG_ContainerFileRetention](
[ContainerFileRetentionID] [INT] IDENTITY(1,1) NOT NULL,
[ContainerName] [NVARCHAR](250) NOT NULL,
[Prefix] [NVARCHAR](100) NOT NULL,
[DynamicRetentionPolicyID] [INT] NOT NULL,
[NoNewFileWithinHoursAlert] [INT] NOT NULL,
[FilesRetentionPeriod] [INT] NOT NULL,
[RetentionPolicyActive] [BIT] NOT NULL,
[StorageAccountName] [NVARCHAR](250) NULL,
[StorageAccountSubscriptionID] [UNIQUEIDENTIFIER] NULL,
[Comments] [NVARCHAR](250) NULL,
CONSTRAINT [PK_CFG_ContainerFileRetention_ContainerFileRetentionID] PRIMARY KEY CLUSTERED
(
[ContainerFileRetentionID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_ContainerFileRetention_Prefix]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CFG_ContainerFileRetention] ADD CONSTRAINT [CFG_ContainerFileRetention_Prefix] DEFAULT ('*') FOR [Prefix]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_ContainerFileRetention_DynamicRetentionPolicyID]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CFG_ContainerFileRetention] ADD CONSTRAINT [CFG_ContainerFileRetention_DynamicRetentionPolicyID] DEFAULT ((0)) FOR [DynamicRetentionPolicyID]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_ContainerFileRetention_NoNewFileWithinHoursAlert]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CFG_ContainerFileRetention] ADD CONSTRAINT [CFG_ContainerFileRetention_NoNewFileWithinHoursAlert] DEFAULT ((0)) FOR [NoNewFileWithinHoursAlert]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_ContainerFileRetention_RetentionPeriod]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CFG_ContainerFileRetention] ADD CONSTRAINT [CFG_ContainerFileRetention_RetentionPeriod] DEFAULT ((3650)) FOR [FilesRetentionPeriod]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CFG_ContainerFileRetention_RetentionPolicyActive]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[CFG_ContainerFileRetention] ADD CONSTRAINT [CFG_ContainerFileRetention_RetentionPolicyActive] DEFAULT ((0)) FOR [RetentionPolicyActive]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LOG_ContainerFileRetentionRemovals]') AND type IN (N'U'))
BEGIN
CREATE TABLE [dbo].[LOG_ContainerFileRetentionRemovals](
[ContainerFileRetentionRemovalID] [BIGINT] IDENTITY(1,1) NOT NULL,
[ContainerFileRetentionID] [INT] NOT NULL,
[BlobFileName] [NVARCHAR](MAX) NULL,
[DateRemoved] [DATETIME2](7) NULL,
CONSTRAINT [PK__LOG_ContainerFileRetentionRemovals] PRIMARY KEY CLUSTERED
(
[ContainerFileRetentionRemovalID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__LOG_ContainerFileRetentionRemovals_DateRemoved]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[LOG_ContainerFileRetentionRemovals] ADD CONSTRAINT [DF__LOG_ContainerFileRetentionRemovals_DateRemoved] DEFAULT (GETUTCDATE()) FOR [DateRemoved]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LOG_ContainerFileRetentionRemovals_ContainerFileRetentionID]') AND parent_object_id = OBJECT_ID(N'[dbo].[LOG_ContainerFileRetentionRemovals]'))
ALTER TABLE [dbo].[LOG_ContainerFileRetentionRemovals] WITH CHECK ADD CONSTRAINT [FK_LOG_ContainerFileRetentionRemovals_ContainerFileRetentionID] FOREIGN KEY([ContainerFileRetentionID])
REFERENCES [dbo].[CFG_ContainerFileRetention] ([ContainerFileRetentionID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LOG_ContainerFileRetentionRemovals_ContainerFileRetentionID]') AND parent_object_id = OBJECT_ID(N'[dbo].[LOG_ContainerFileRetentionRemovals]'))
ALTER TABLE [dbo].[LOG_ContainerFileRetentionRemovals] CHECK CONSTRAINT [FK_LOG_ContainerFileRetentionRemovals_ContainerFileRetentionID]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TMP_DynamicRetentionFileWorker]') AND type IN (N'U'))
BEGIN
CREATE TABLE [dbo].[TMP_DynamicRetentionFileWorker](
[DynamicRetentionFileWorkerID] [BIGINT] IDENTITY(1,1) NOT NULL,
[FileURI] [NVARCHAR](MAX) NOT NULL,
[FileDate] [DATETIME2](7) NOT NULL,
[DynamicGrouper] [NVARCHAR](20) NULL,
[DynamicAction] [NVARCHAR](30) NULL,
[PathGrouper] [NVARCHAR](MAX) NULL,
[FileGroupCounter] [INT] NULL,
PRIMARY KEY CLUSTERED
(
[DynamicRetentionFileWorkerID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
INSERT INTO dbo.CFG_DynamicRetentionPolicies ( DynamicRetentionPolicyID ,
DRPStep ,
CutOffStart ,
CutOffEnd ,
RetentionAction )
VALUES ( 1, 2, 14, 365, N'Week' ) ,
( 1, 3, 366, 730, N'Month' ) ,
( 1, 4, 731, 2190, N'Annual' ) ,
( 1, 5, 2191, NULL, N'Delete' )
/****** Object: StoredProcedure [dbo].[PRC_ListActiveContainerFileRetentions] Script Date: 2018-04-19 19:08:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PRC_ListActiveContainerFileRetentions]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[PRC_ListActiveContainerFileRetentions] AS'
END
GO
ALTER PROCEDURE [dbo].[PRC_ListActiveContainerFileRetentions]
(
@ListType NVARCHAR(20) = 'Standard'
)
AS
BEGIN
SET NOCOUNT ON
IF @ListType = 'Dynamic'
BEGIN
SELECT ccfr.ContainerFileRetentionID ,
ccfr.ContainerName ,
ccfr.Prefix ,
ccfr.FilesRetentionPeriod ,
ccfr.StorageAccountName ,
ccfr.StorageAccountSubscriptionID ,
ccfr.DynamicRetentionPolicyID ,
ccfr.NoNewFileWithinHoursAlert
FROM dbo.CFG_ContainerFileRetention AS ccfr
WHERE ccfr.RetentionPolicyActive = 1
AND ccfr.DynamicRetentionPolicyID > 0
AND ccfr.DynamicRetentionPolicyID IN ( SELECT cdrp.DynamicRetentionPolicyID
FROM dbo.CFG_DynamicRetentionPolicies AS cdrp )
END
IF @ListType = 'Standard'
BEGIN
SELECT ccfr.ContainerFileRetentionID ,
ccfr.ContainerName ,
ccfr.Prefix ,
ccfr.FilesRetentionPeriod ,
ccfr.StorageAccountName ,
ccfr.StorageAccountSubscriptionID ,
ccfr.DynamicRetentionPolicyID ,
ccfr.NoNewFileWithinHoursAlert
FROM dbo.CFG_ContainerFileRetention AS ccfr
WHERE ccfr.RetentionPolicyActive = 1
AND ccfr.DynamicRetentionPolicyID = 0
AND ccfr.FilesRetentionPeriod > 0
END
IF @ListType = 'NewFileAlert'
BEGIN
SELECT ccfr.ContainerFileRetentionID ,
ccfr.ContainerName ,
ccfr.Prefix ,
ccfr.FilesRetentionPeriod ,
ccfr.StorageAccountName ,
ccfr.StorageAccountSubscriptionID ,
ccfr.DynamicRetentionPolicyID ,
ccfr.NoNewFileWithinHoursAlert
FROM dbo.CFG_ContainerFileRetention AS ccfr
WHERE ccfr.RetentionPolicyActive = 1
AND ccfr.NoNewFileWithinHoursAlert > 0
END
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PRC_LogContainerFileRetentionFileRemoval]') AND type IN (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[PRC_LogContainerFileRetentionFileRemoval] AS'
END
GO
ALTER PROCEDURE [dbo].[PRC_LogContainerFileRetentionFileRemoval]
(
@ContainerFileRetentionID INT ,
@BlobFileName NVARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.LOG_ContainerFileRetentionRemovals ( ContainerFileRetentionID ,
BlobFileName )
VALUES ( @ContainerFileRetentionID, @BlobFileName )
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PRC_DynamicRetention]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[PRC_DynamicRetention] AS'
END
GO
ALTER PROCEDURE [dbo].[PRC_DynamicRetention]
(
@DynamicRetentionPolicyID INT
)
AS
BEGIN
SET NOCOUNT ON
SET DATEFIRST 1
DECLARE @CurrentDate DATE = GETUTCDATE()
UPDATE tltrf
SET tltrf.PathGrouper = REVERSE(
SUBSTRING(
REVERSE(tltrf.FileURI) ,
CHARINDEX('/', REVERSE(tltrf.FileURI))
+ 1,
99999))
FROM dbo.TMP_DynamicRetentionFileWorker AS tltrf
UPDATE tdrfw
SET tdrfw.DynamicGrouper = CASE cdrp.RetentionAction
WHEN 'Week' THEN
CAST(DATEPART(
YEAR , tdrfw.FileDate) AS NVARCHAR(4))
+ '-'
+ CAST(DATEPART(
WEEK , tdrfw.FileDate) AS NVARCHAR(2))
WHEN 'Month' THEN
CAST(DATEPART(
YEAR , tdrfw.FileDate) AS NVARCHAR(4))
+ '-'
+ CAST(DATEPART(
MONTH , tdrfw.FileDate) AS NVARCHAR(2))
WHEN 'Annual' THEN
CAST(DATEPART(
YEAR , tdrfw.FileDate) AS NVARCHAR(4))
END ,
tdrfw.DynamicAction = cdrp.RetentionAction
FROM dbo.TMP_DynamicRetentionFileWorker AS tdrfw
INNER JOIN dbo.CFG_DynamicRetentionPolicies AS cdrp ON cdrp.DynamicRetentionPolicyID = @DynamicRetentionPolicyID
AND CAST(tdrfw.FileDate AS DATE)
BETWEEN DATEADD(
DAY ,
-ISNULL(
cdrp.CutOffEnd ,
9999),
@CurrentDate) AND DATEADD(
DAY ,
-cdrp.CutOffStart,
@CurrentDate)
DELETE tdrfw
FROM dbo.TMP_DynamicRetentionFileWorker AS tdrfw
WHERE tdrfw.DynamicAction IS NULL
UPDATE tdrfw
SET tdrfw.FileGroupCounter = counts.FileGroupCounter
FROM dbo.TMP_DynamicRetentionFileWorker AS tdrfw
INNER JOIN ( SELECT tdrfw.DynamicRetentionFileWorkerID ,
ROW_NUMBER() OVER ( PARTITION BY tdrfw.PathGrouper ,
tdrfw.DynamicAction ,
tdrfw.DynamicGrouper
ORDER BY tdrfw.FileDate DESC ) AS FileGroupCounter
FROM dbo.TMP_DynamicRetentionFileWorker AS tdrfw
WHERE tdrfw.DynamicAction NOT IN ('Archive' ,
'Delete')) counts ON counts.DynamicRetentionFileWorkerID = tdrfw.DynamicRetentionFileWorkerID
UPDATE tdrfw
SET tdrfw.DynamicAction = CASE tdrfw.FileGroupCounter
WHEN 1 THEN 'Keep'
ELSE 'Delete'
END
FROM dbo.TMP_DynamicRetentionFileWorker tdrfw
WHERE tdrfw.DynamicAction NOT IN ('Archive', 'Delete')
SELECT TMP_DynamicRetentionFileWorker.FileURI
FROM dbo.TMP_DynamicRetentionFileWorker
WHERE TMP_DynamicRetentionFileWorker.DynamicAction = 'Delete'
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RPT_ContainerFileRetentionRemovals]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RPT_ContainerFileRetentionRemovals] AS'
END
GO
ALTER PROCEDURE [dbo].[RPT_ContainerFileRetentionRemovals]
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManyFilesRemoved TABLE
(
StorageAccountName NVARCHAR(250) ,
ContainerName NVARCHAR(250) ,
BlobPrefix NVARCHAR(100) ,
RemovalCount BIGINT
)
INSERT INTO @ManyFilesRemoved ( StorageAccountName ,
ContainerName ,
BlobPrefix ,
RemovalCount )
SELECT ccfr.StorageAccountName ,
ccfr.ContainerName ,
ccfr.Prefix AS BlobPrefix ,
COUNT(*) AS RemovalCount
FROM dbo.LOG_ContainerFileRetentionRemovals AS lcfrr
INNER JOIN dbo.CFG_ContainerFileRetention AS ccfr ON ccfr.ContainerFileRetentionID = lcfrr.ContainerFileRetentionID
WHERE lcfrr.DateRemoved >= DATEADD(
HOUR ,-24, GETUTCDATE())
GROUP BY ccfr.StorageAccountName ,
ccfr.ContainerName ,
ccfr.Prefix
HAVING COUNT(*) > 10
SELECT ccfr.StorageAccountName ,
ccfr.ContainerName ,
ccfr.Prefix AS BlobPrefix ,
lcfrr.BlobFileName
FROM dbo.LOG_ContainerFileRetentionRemovals AS lcfrr
INNER JOIN dbo.CFG_ContainerFileRetention AS ccfr ON ccfr.ContainerFileRetentionID = lcfrr.ContainerFileRetentionID
LEFT OUTER JOIN @ManyFilesRemoved AS mfr ON mfr.ContainerName = ccfr.ContainerName
AND ccfr.Prefix = mfr.BlobPrefix
WHERE lcfrr.DateRemoved >= DATEADD(HOUR, -24, GETUTCDATE())
AND mfr.RemovalCount IS NULL
UNION ALL
SELECT mfr.StorageAccountName ,
mfr.ContainerName ,
mfr.BlobPrefix ,
'More Than 10 Files Removed [Files Removed : '
+ CAST(mfr.RemovalCount AS NVARCHAR(10))
+ '] See Removals Log For Details.'
FROM @ManyFilesRemoved AS mfr
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PRC_CheckAndAddContainerForManagedStorageAccountRetention]') AND type IN (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[PRC_CheckAndAddContainerForManagedStorageAccountRetention] AS'
END
GO
ALTER PROCEDURE [dbo].[PRC_CheckAndAddContainerForManagedStorageAccountRetention]
(
@StorageAccountName NVARCHAR(100) ,
@StorageAccountSubscriptionID UNIQUEIDENTIFIER ,
@ContainerName NVARCHAR(250)
)
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS ( SELECT 1
FROM dbo.CFG_ContainerFileRetention AS ccfr
WHERE ccfr.StorageAccountName = @StorageAccountName
AND ccfr.StorageAccountSubscriptionID = @StorageAccountSubscriptionID
AND ccfr.ContainerName = @ContainerName )
BEGIN
INSERT INTO dbo.CFG_ContainerFileRetention ( ContainerName ,
Prefix ,
FilesRetentionPeriod ,
StorageAccountName ,
StorageAccountSubscriptionID ,
Comments )
VALUES ( @ContainerName , -- ContainerName - nvarchar(250)
N'*' , -- Prefix - nvarchar(100)
-1 , -- FilesRetentionPeriod - int
@StorageAccountName , -- StorageAccountName - nvarchar(250)
@StorageAccountSubscriptionID , -- StorageAccountSubscriptionID - uniqueidentifier
N'Automatically Generated - New Container Added In Storage Account' -- Comments - nvarchar(250)
)
END
END
GO
April 23, 2018 at 12:36 am
This was removed by the editor as SPAM
July 9, 2018 at 1:55 pm
Hi Higgim,
I tried the powershell script to copy backup files to Azure, but I got an error, not sure how to fix it:
....
Checking for container [...] in Storage Account [... (Will attempt create if container not present)
Exception calling "Substring" with "1" argument(s): "startIndex cannot be larger than length of string.
Parameter name: startIndex"
At G:\psscript\CopyBackupFilesToAzure.ps1:196 char:3
+ $StorageAccountSubDirectory = ( ( ( ($File.DirectoryName.Replace("$Sou ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: ( : ) [], MethodInvocationException
+ FullyQualifiedErrorId : ArgumentOutOfRangeException
Processing File [B:\BACKUP\MYServer\mydb\FULL\MYSERVER_MYDB_FULL_.bak] : Status [ Success]
Could you take a look please?
Also I see in Azure portal, under the newly created container name, there is a subfolder the folder name is empty with the word no name. I think this may cause the error.
But how come the folder name is empty?
Thanks
July 10, 2018 at 2:00 am
Hi
Long shot but for your SourceDirectory parameter did you put a / on the end? If so, can you remove it?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply