Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.