# Compare SQL Server databases with backup folder names
# This script identifies backup folders for databases that no longer exist
# Configuration
$SQLServer = "localhost" # e.g., "SQL01" or "SQL01\INSTANCE"
$BackupPath = "C:\SQLBackups\SQLInstace" # Main backup folder path
# Get existing databases from SQL Server
Write-Host "Connecting to SQL Server: $SQLServer" -ForegroundColor Cyan
try {
$query = @"
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY name
"@
$existingDatabases = Invoke-Sqlcmd -ServerInstance $SQLServer -Query $query -ErrorAction Stop
$dbNames = $existingDatabases | Select-Object -ExpandProperty name
Write-Host "Found $($dbNames.Count) user databases on SQL Server" -ForegroundColor Green
}
catch {
Write-Host "Error connecting to SQL Server: $_" -ForegroundColor Red
exit 1
}
# Get backup folder names
Write-Host "`nScanning backup path: $BackupPath" -ForegroundColor Cyan
try {
$backupFolders = Get-ChildItem -Path $BackupPath -Directory -ErrorAction Stop
Write-Host "Found $($backupFolders.Count) backup folders" -ForegroundColor Green
}
catch {
Write-Host "Error accessing backup path: $_" -ForegroundColor Red
exit 1
}
# Compare and identify orphaned backup folders
Write-Host "`n=== COMPARISON RESULTS ===" -ForegroundColor Yellow
# System databases to exclude
$systemDatabases = @('master', 'model', 'msdb', 'tempdb', 'distribution', 'reportserver', 'reportservertempdb')
$orphanedBackups = @()
$matchingBackups = @()
$systemBackups = @()
foreach ($folder in $backupFolders) {
$folderName = $folder.Name
# Skip system database folders
if ($systemDatabases -contains $folderName) {
$systemBackups += $folderName
continue
}
if ($dbNames -contains $folderName) {
$matchingBackups += $folderName
}
else {
$orphanedBackups += [PSCustomObject]@{
FolderName = $folderName
FullPath = $folder.FullName
CreationTime = $folder.CreationTime
LastWriteTime = $folder.LastWriteTime
SizeMB = [math]::Round((Get-ChildItem -Path $folder.FullName -Recurse -File -ErrorAction SilentlyContinue |
Measure-Object -Property Length -Sum).Sum / 1MB, 2)
}
}
}
# Display results
Write-Host "`nSystem database backups (excluded): $($systemBackups.Count)" -ForegroundColor Cyan
if ($systemBackups.Count -gt 0) {
$systemBackups | Sort-Object | ForEach-Object { Write-Host " - $_" -ForegroundColor DarkGray }
}
Write-Host "`nDatabases with matching backups: $($matchingBackups.Count)" -ForegroundColor Green
if ($matchingBackups.Count -gt 0) {
$matchingBackups | Sort-Object | ForEach-Object { Write-Host " - $_" -ForegroundColor Gray }
}
Write-Host "`nOrphaned backup folders (no matching database): $($orphanedBackups.Count)" -ForegroundColor Magenta
if ($orphanedBackups.Count -gt 0) {
$orphanedBackups | Sort-Object FolderName | Format-Table -AutoSize FolderName, LastWriteTime, SizeMB
# Calculate total space
$totalSizeMB = ($orphanedBackups | Measure-Object -Property SizeMB -Sum).Sum
$totalSizeGB = [math]::Round($totalSizeMB / 1024, 2)
Write-Host "Total space used by orphaned backups: $totalSizeMB MB ($totalSizeGB GB)" -ForegroundColor Yellow
}
# Export to CSV for further analysis
$reportPath = "C:\Temp\OrphanedBackups_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
if ($orphanedBackups.Count -gt 0) {
$orphanedBackups | Export-Csv -Path $reportPath -NoTypeInformation
Write-Host "`nDetailed report exported to: $reportPath" -ForegroundColor Cyan
}
# Optional: Ask for confirmation to delete
Write-Host "`n=== CLEANUP OPTIONS ===" -ForegroundColor Yellow
Write-Host "To delete orphaned backups, uncomment and run the cleanup section below" -ForegroundColor Gray
<#
# CLEANUP SECTION - UNCOMMENT TO ENABLE
$confirm = Read-Host "`nDo you want to DELETE these orphaned backup folders? (Type 'DELETE' to confirm)"
if ($confirm -eq "DELETE") {
Write-Host "`nDeleting orphaned backup folders..." -ForegroundColor Red
foreach ($backup in $orphanedBackups) {
try {
Remove-Item -Path $backup.FullPath -Recurse -Force
Write-Host " Deleted: $($backup.FolderName)" -ForegroundColor Green
}
catch {
Write-Host " Failed to delete $($backup.FolderName): $_" -ForegroundColor Red
}
}
}
else {
Write-Host "Cleanup cancelled" -ForegroundColor Yellow
}
#>
Comments
Post a Comment