PowerShell Script: Backup File Check & Removal

PowerShell Script: Backup File Check & Removal

Please click the +1 button if you find this post helpful.

Recently a client with a rather large set of databases started having some issues maintaining sufficient disk space, which was causing backups to fail. Not wanting to spend money on SQL specific agents for their backup system, SQL jobs were created to backup to disk, where they would be picked up by the backup server and written off to tape.

The problem is the process of reviewing the log files, ensuring backups were written to tape, then clearing the disk based backups was a manual process requiring significant time on a daily basis. Also, whenever this process was skipped drive space would fill up, and backups would be missed, resulting in the inability to meet specific RPO's.

While it's not the prettiest solution, the PowerShell script below was written to loop through the directory where the backup files reside, then for each file found in the directory, search the tape backup log file for the file name, date and indication of a "sent" status. If these conditions were met, the file could be deleted. The script below, takes input for the Backup Directory Path, Log File, Location to save a transaction log, Date.

Since management wasn't very trusting of automation scripts, the action taken on any file needed to be easiliy turnned on or off. Line 44, 45, and 46 can be commented out to prevent any files from being deleted, but output will still indicate matches found on screen and in the log file.

USA RingCentral Office - Your complete all-inclusive phone system. Try RingCentral free for 30 days.

Because of how this script was written, it can be fairly easily modified to search for a variety of entries in a log file, take customized actions, and limit or expand the scope of the search.

################################################################
################################################################
###
###  TITLE:   Backup File Checker
###  AUTHOR:  England, Matthew D.
###  DATE:    20130705
###  DESCRIPTION:
###           Check's log file identified for inidcaton of
###           backup file sent successfully to tape. Creates
###           report indicating transaction then removes
###           associated backup files on disk.
###
################################################################
################################################################
Set-StrictMode -Version latest

$path = Read-Host "Enter PATH to Backup Files. End line with backslash \"
$files = Get-Childitem $path #*.bak #<<<-uncomment extension to limit to a specific file type such as bak or trn.
$logs = Read-Host "Enter PATH and FILENAME of TSM Log File dsmsched.log"
$actionlog = (Read-Host "Enter PATH for Log File") + "BackupRollAction.log"
$ReviewDate = Get-Date ((Get-Date).AddDays((Read-Host "Enter how many days from today to use as the date for log checking. Ex: -1 = Yesterday"))) -Format MM/dd/yyyy # Requests user input to specify log review date
$Status = "Sent"
$Operator = Read-Host "Enter UserID"

Function Get-BackupRollAction
{
  # LOOP THROUGH DIRECTORY OF BACKUP FILES
  Foreach ($file In $files)
  {
    # LOOP THROUGH EACH LINE OF LOG FILE TO LOCATE MATCH ON DATE, FILE AND STATUS
    ForEach ($line In $logs)
    {
      $logentry = Get-Content $logs |Select-String -Pattern $ReviewDate| Select-String -Pattern $file |Select-String -Pattern $Status
      If ($logentry -cmatch $file)
      {
      # WRITE ALL MATCHING FILES TO LOG AND DISPLAY ON SCREEN.
      "FOUND :  $path$file  in log $logs :" | Out-File $actionlog -Append
      Write-Host "FOUND :  $path$file  in log $logs :" -ForegroundColor Yellow
      #TAKE ACTION ON MATCHED FILES. DELETE TO CLEAR DRIVE SPACE
      #THE FOLLOWING THREE LINES MAY BE COMMENTED OUT IF NO DELETE ACTION IS TO BE TAKEN ON A FILE
      Remove-Item $path$file
      "DELETED :  $path$file  in log $logs : By Operator $Operator" | Out-File $actionlog -Append
      Write-Host "Deleted: $path$file" -ForegroundColor Red
      }
      
    }
  }
}

################################################################
################################################################

If you have questions, or suggestions for modification of this script, please post a comment.