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.