Are My Backups Out of Control?

Recently, I moved our database backup share from our old SAN to a new SAN that my company just purchased.  The backups were running faster and everything was just great. However, one day our SAN administrator approached me and said that we were running out of space on the backup share.  Now, usually I am pretty good about the maintenance on the share.  But, it got me wondering if indeed I had screwed something up, such as the schedules of the backups or set them to retain longer than normal.  So, I started manually checking the folder sizes, which represented each server, on the share.  And since there were quite a few folders, this activity started taking a long time and I knew there had to be a better way.

So, I turned to my trusty friend … PowerShell … with two main goals in mind:
1. Find the total size of the contents of each folder
2. Find the files that are older than a certain date

Find Folder Size
As mentioned, my first goal was to find the size of the child folders of the share, with all their contents and sub-folders summed up.  The key piece of code that I used to find these sizes is the Get-ChildItem cmdlet (

$directory = "\\share\root"
$folders = Get-ChildItem $directory

foreach($folder in $folders)
Get-ChildItem $folder.FullName -Recurse | Measure-Object -Sum Length |
Select-Object @{Name="Path";Expression={$folder.FullName}},

Find Old Files
My second goal was to find files that are older than a certain date and then delete them. Realistically, this process should never have to delete anything because my database backup script is supposed to delete the files after it takes a new backup.  However, if a database is removed or moved to a new server, and the backup share is not manually cleaned up, those backup files could just sit out there.  So, this script would be intended to find those cases, remove them, and log the results to a file.

$directory = "\\share\root"
$numdays = 8

$files = Get-ChildItem FileSystem::$directory -Recurse
$todaysdate = Get-Date
$count = 0

$output = "BEGIN: Delete files older than " + $todaysdate.AddDays(-$numdays) + " from " + $directory
$outputfile = "\\share\root\file_cleanup\DeleteBackupFiles_" + ($todaysdate.ToShortDateString()) + ".txt"
$outputfile = $outputfile.Replace("/","_")

$output | Out-File FileSystem::$outputfile -Append

foreach($file in $files)
$fileage = ($todaysdate - $file.CreationTime).Days
if ($fileage -gt ($numdays - 1) -and $file.PSIsContainer -ne $true)
$output = "- DELETE: " + $file.FullName
$output | Out-File FileSystem::$outputfile -Append
$count = $count + 1

$output = "END: " + $count + " files deleted."
$output | Out-File FileSystem::$outputfile -Append

After I had written both scripts, I noticed that my backups were not taking any more space, and there were not old copies of backups on the share.  So, I approached my SAN administrator with these facts and he found that the problem was on his end with using SAN snapshots on the share. Crisis adverted … my backups were not out of control!

Final score: DBA – 1, SAN Admin – 0 … 🙂

But, as you can see, using PowerShell is an extremely handy tool to handle basic things such as folder and file maintenance on a backup share.

This entry was posted in DBA, PowerShell and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s