SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Powershell script to report old backup files

By Rolando Hernandez,

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 "serverlist.txt".  

        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
USE master

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.


Total article views: 989 | Views in the last 30 days: 1
Related Articles

Script to create datestamp folder causing issue

Script to create datestamp folder causing issue


Creating Folders Using VB and Recursion

Read this article to learn how to use recursion to simply the task of creating multiple levels of fo...


Script for Creating backup sp before modifying orginal sp

Script for Creating backup sp before modifying orginal sp


PowerShell Query to remove backup folders against multiple servers

Remove backup folders from multiple servers.


Script to restore red gate backups automatically from a folder.

Restore of red gate backup files using a SQL Script

old files