Technical Article

Powershell script to report old backup files

,

Get a report of any file older than 60 days.

Is very common to create backup files that for one reason or other are not in the focus of the backup purge routines, aditional to this, in order to monitor the optimal space I wrote this powershell routine to find old backup files or other related expired files.

The script creates a and emails the report in HTML format.

Directions

1)  Create a folder or a repository fo the "old_backups.ps1" powershell script.

        Example:

               E:\MyPSScripts\old_backups.ps1

2)  In this folder create a text file called "serverlist.txt".  

        Add all your SQL servers where you want to search for old files, see example below.

       Winbox2003R21.windowslab.COM,5433

       Winbox2012AB.windowslab.COM,6433

       Winbox2012Z4.windowslab.COM,6433

       These servers will be used to find old files.

3)  Create a SQL file called "script.sql" with this code :

/*
    Script to get backup folder name
    Description :  leaves the folder only, replaces ":" for "$" and also replaces "\" for "\\" for powershell sake
*/USE master
go
DECLARE @HostName AS VARCHAR(50)
SELECT @HostName = CAST(serverproperty('machinename') AS VARCHAR(50))
SELECT DISTINCT '\\\\' + @HostName + '\\' + REPLACE( REPLACE( UPPER( SUBSTRING(phyname, 1, LEN(phyname) - LEN( SUBSTRING(phyname, LEN(phyname) - CHARINDEX('\',REVERSE(phyname)) + 2, LEN(phyname) ) ))),'\','\\'), ':','$')   from sysdevices

This sql script is an input for the powershell to read the sys.devices in the target SQL Servers,  sysdevices is the common system table to read for backups.

How to run the script from MSDOS

Run this code using the following command from.

          CD "E:\MyPSScripts\"
     powershell -command E:\MyPSScripts\old_backups.ps1

After you run this, you'll notice some new files generated in the "E:\MyPSScripts\" folder;

                errorlog.out                -   Output of any runtime error.  If exection is clean, the file will be blank.

                60dayoldfiles.HTML   -  HTML report of old files.

Report generated from the script

If everything went fine, you will get the report to your email or to an HTML file like the following :

I hope this likes to your management and to you.

Thanks for reading.

Rolando.

#Powershell script to create a report of backup files older than 60 days.
# with the option to email it, or just save it.
#
#**Requirements:  SQL Server 2008 snapins (Usually installed with sql client tools)
#If you need to change the age criteria, change the variable <$60DaysAgo> to your needs.
#read the section for <#Send email> to check for the email messaging options.
#
#**file inputs/outputs :
#$sqlfile  - contains the SQL Script to read the target folder of backups files
#$serverlist - contains the list of servers to be checked against old backup files.
#$60dayOldFiles - output file with the HTML formatted report.
#   $ErrorLog - output file containing the list of runtime errors

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

[string]$serverlist = pwd
$serverlist = $serverlist + "\serverlist.txt"

[string]$sqlfile = pwd
$sqlfile = $sqlfile + "\script.sql"

[string]$reportfile = pwd
$reportfile = $reportfile + "\60dayoldfiles.html"

[string]$ErrorLine
[String]$ErrorLog = pwd
$ErrorLog = $ErrorLog + "\errorlog.out"

#Variable containing a date 60 days ago
$60DaysAgo = [DateTime]::Now.AddDays(-60)

#define output arrays
[string]$Backup_folder_tmp
$Full_Backups_folder_array=@()
$60dayOldFiles = @()

$head = '<style type="text/css">
td {font-family:Arial,Verdana,Sans-serif;font-size:13px };
th {font-family:Arial,Verdana,Sans-serif;font-size:13px;
background-color:beige}</style>'

$header = "<H2>Report of backup files older than 60 days</H2>"

$title = "Backup files older than 60 days"


#initialize log file if it exists
Get-ChildItem $ErrorLog | Measure-Object | foreach-object{ if($_.count -gt 0){ Clear-Content -path $ErrorLog } }

#get server list from file and process each server line
Get-Content -Path $serverlist | ForEach-Object { 
 $Backup_folder = Invoke-Sqlcmd -InputFile $sqlfile -ServerInstance $_ -Database "master" -ErrorVariable sqlError -ErrorAction Silentlycontinue; 
 $Full_Backups_folder_array += $Backup_folder;  
 if ($sqlError){ Add-content -path $ErrorLog -value "Error on server : $_"} 
}


#alternative 2
$Full_Backups_folder_array | 
ForEach-Object { 
$60dayOldFiles += Get-ChildItem $_.column1 | where-object {$_.lastwritetime -le $60DaysAgo -AND ! $_.PSIsContainer } | select-object FullName, creationtime, LastWriteTime, length
}

#export variable to a comma separated file
$60dayOldFiles | Export-Csv -path $reportfile  -noTypeInformation -force

$60dayOldFiles | ConvertTo-HTML -head $head -body $header -title $title | out-file $reportfile

#Add error report to errorlog
Add-content -path $ErrorLog -value $Error

$mailbody = get-content $reportfile
[string]$sqlText = "EXEC msdb.dbo.sp_send_dbmail @recipients='recipient@mydomain.com', @subject = 'Report of Backups older than 60 days', @body = '" + $mailbody  + "', @body_format = 'HTML' ;"

#Send email
Invoke-Sqlcmd -Query $sqlText -ServerInstance "MySQLServer,1433" -Database "msdb";

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating