Remove-SqlBackups.ps1

function Remove-SQLBackups {
    <#
            .SYNOPSIS
                Removes old backups stored
            .DESCRIPTION
                The function removes old backups taken using Ola Hallengren maintenance solution. It
                also takes care of properly removing the backups based on Production, Development
                and Test retention policies.
            .PARAMETER RootBackupShare
                File share where the SQL Server backups are setup. This should be the same as
                reported in the Ola Hallengren backup jobs
            .PARAMETER ProdRentention
                Number of days specified for the Production rentention of backups.                
            .PARAMETER DevTestRentention
                Number of days specified for the Development and Testing rentention of backups.                
            .PARAMETER RepositoryServer
                Name of the repository server where the inventory database is located
            .PARAMETER RepositoryDatabase
                Name of the database where the server inventory information is contained 
            .NOTES
                Tags: dbatools, Remove Backups,
                Author: Marcos Freccia
                Website: https://github.com/marcosfreccia
                License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
            
            .EXAMPLE
                PS C:\> Remove-SQLBackups -RootBackupShare "\\fileserver001\SQL_BACKUP" -ProdRentention 16d -DevTestRentention 5d -RepositoryServer 'MyRepositoryServer' -RepositoryDB MyRepositoryDB
                The Following script will retrieve the PROD, DEV and TEST Servers from MyRepositoryDB database hosted on MyRepositoryServer and it will perform the backup deletion based in the retention policies defined on ProdRentention and DevTestRentention parameters
        #>    
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)][string]$RootBackupShare,
        [Parameter(Mandatory)][string]$ProdRentention,
        [Parameter(Mandatory)][string]$DevTestRentention,
        [Parameter(Mandatory)][string]$RepositoryServer,
        [Parameter(Mandatory)][string]$RepositoryDatabase
    )

    process {
        Write-Verbose -Message "Connecting to $RepositoryDatabase to retrieve the list of standalone servers "
    
        $Servers = Invoke-Sqlcmd2 -ServerInstance $RepositoryServer -Database $RepositoryDatabase -Query "SELECT ServerName,SrvRole,TCPPort FROM dbo.Servers WHERE SrvRole NOT IN ( 'REPORTING' ) AND Node1 IS NULL AND Node2 IS NULL;"
    
        Write-Verbose -Message "Retrieved $($Servers.Count) servers"

        foreach ($server in $Servers) {
            $server_role = $server.SrvRole
            $server_name = $server.ServerName
        
            Write-Verbose -Message "Working on $server_name"


            try {
                switch ($server_role) {
                    'DEVELOPMENT' {
                        Write-Verbose -Message "Server is categorized as $server_role)"
                        Write-Verbose -Message "Removing backups..."
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension bak -RetentionPeriod $DevTestRentention
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension trn -RetentionPeriod $DevTestRentention

                    }
                    'TEST' {
                        Write-Verbose -Message "Server is categorized as $server_role)"
                        Write-Verbose -Message "Removing backups..."
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension bak -RetentionPeriod $DevTestRentention
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension trn -RetentionPeriod $DevTestRentention

                    }
                    'PRODUCTION' {
                        Write-Verbose -Message "Server is categorized as $server_role)"
                        Write-Verbose -Message "Removing backups..."
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension bak -RetentionPeriod $ProdRentention
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension trn -RetentionPeriod $ProdRentention 
                    }
                    'MANAGEMENT' {
                        Write-Verbose -Message "Server is categorized as $server_role)"
                        Write-Verbose -Message "Removing backups..."
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension bak -RetentionPeriod $ProdRentention
                        Remove-DbaBackup -Path "$RootBackupShare\$server_name\" -BackupFileExtension trn -RetentionPeriod $ProdRentention 
                    }
                }
            }
            catch {
                $server_name = $server.ServerName + ',' + $server.TCPPort
                Write-Error "This is the problem: $server_name"  
            }
        }

        Write-Verbose -Message "Connecting to $RepositoryDatabase to retrieve the list of clustered servers"
    
        $Clusters = Invoke-Sqlcmd2 -ServerInstance $RepositoryServer -Database $RepositoryDatabase -Query "SELECT ServerName,SrvRole,TCPPort FROM dbo.Servers WHERE SrvRole NOT IN ( 'REPORTING' ) AND Node1 IS NOT NULL AND Node2 IS NOT NULL;"
    
        Write-Verbose -Message "Retrieved $($Clusters.Count) Clusters"

        foreach ($cluster in $Clusters) {
            $cluster_name = $cluster.ServerName
            $cluster_role = $cluster.SrvRole

            Write-Verbose "Working on $cluster_name"

            try {
                $Fullserver_name = $cluster.ServerName + ',' + $cluster.TCPPort

                $ClusterAndAGName = Invoke-Sqlcmd2 -ServerInstance $Fullserver_name -Database master -Query "SELECT DISTINCT
               CONCAT(hr.cluster_name, '$', ag.name) AS ClusterAGName
               FROM sys.dm_hadr_cluster AS hr,
               sys.availability_groups AS ag
               JOIN sys.availability_databases_cluster AS adc
               ON adc.group_id = ag.group_id;"

                $cluster_ag_name = $ClusterAndAGName.ClusterAGName

                if ($cluster_ag_name) {
                    switch ($cluster_role) {
                        'PRODUCTION' {
                            Write-Verbose -Message "Cluster is categorized as $cluster_role"
                            Write-Verbose -Message "Removing backups..."                
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension bak -RetentionPeriod $ProdRentention
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension trn -RetentionPeriod $ProdRentention
                        }
                        'DEVELOPMENT' {
                            Write-Verbose -Message "Cluster is categorized as $cluster_role"
                            Write-Verbose -Message "Removing backups..."                
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension bak -RetentionPeriod $DevTestRentention
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension trn -RetentionPeriod $DevTestRentention
                        }
                        'TEST' {
                            Write-Verbose -Message "Cluster is categorized as $cluster_role"
                            Write-Verbose -Message "Removing backups..."                
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension bak -RetentionPeriod $DevTestRentention
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension trn -RetentionPeriod $DevTestRentention
                        }
                        'MANAGEMENT' {
                            Write-Verbose -Message "Cluster is categorized as $cluster_role"
                            Write-Verbose -Message "Removing backups..."                 
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension bak -RetentionPeriod $ProdRentention
                            Remove-DbaBackup -Path "$RootBackupShare\$cluster_ag_name\" -BackupFileExtension trn -RetentionPeriod $ProdRentention
                        }
                    }
                }
            }
            catch {
                $cluster_name = $cluster_name + ',' + $cluster.tcp_port
                Write-Error "This is the problem: $cluster_name"
            }
        }

    }
}