I wanted to make sure our backup drives were in good shape so needed to quickly filter through all the folders and files on our shared storage backup location and find any old files that stick out. With that i wrote the below script, fill in the top vars ($l for location to search from, $date for amount of days old a file is to be flagged) and let it roll. I have also set it to report on any file thats not a standard SQL Server backup file as well.

<#

Query - Find Old Files 

#>

$Location = "\\Server\SQLBackups"
$Files = gci -Path $Location -Recurse -File
$Date = (get-date).AddDays(-3)
$Results = @()

ForEach ($i in $Files)
 {
 IF ($i.CreationTime -le $Date)
 {
 Write-Host $i.FullName
 $Results += $i
 }
 }

## Report vars
$TotalSize = ($Results | Measure-Object -Sum Length).Sum / 1GB
$TotalFiles = $Results.Count
$FoldersToQuery = $Results.DirectoryName | Select-Object -unique
$BadFileTypes = $Results | Where-Object {$_.Extension -notlike ".trn" -and $_.Extension -notlike ".bak" }

Write-Host "Total Number of Old Files : $TotalFiles" -BackgroundColor DarkGreen -ForegroundColor White
Write-Host "Total Size of Old Files : $TotalSize (GB)" -BackgroundColor DarkGreen -ForegroundColor White
Write-Host "Folders to look into are: " -BackgroundColor DarkGreen -ForegroundColor White
$FoldersToQuery | ft
Write-Host ""
Write-Host "Files that are not BAK in folders:" -BackgroundColor DarkGreen -ForegroundColor White
$BadFileTypes | ft FullName

Advertisements