Do you check your backups?

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

  • matt6288 - Thursday, April 19, 2018 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.

    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

  • This was removed by the editor as SPAM

  • 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

  • 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