/Remove-SqlBackups.ps1
Created Dec 1, 2018
| 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" | |
| } | |
| } | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment