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.
1) Create a folder or a repository fo the
"old_backups.ps1" powershell script.
2) In this folder create a text file called
Add all your SQL servers where you want to search for old files, see example below.
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
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.
After you run this, you'll notice some new files generated in the
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.