Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

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.

Total article views: 700 | Views in the last 30 days: 111
 
Related Articles
FORUM

Script to create datestamp folder causing issue

Script to create datestamp folder causing issue

ARTICLE

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...

FORUM

Script for Creating backup sp before modifying orginal sp

Script for Creating backup sp before modifying orginal sp

FORUM

Script to restore red gate backups automatically from a folder.

Restore of red gate backup files using a SQL Script

FORUM

script for backup files

script for backup files

Tags
backups    
html    
maintenance    
old files    
powershell    
report    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones