How to manage SQL BAK files with Powershell

Managed SQL Server ad-hoc BAK files is imporatnt for disk space managent. Many times Production servers have experienced outages due to ad-hoc SQL backup files left on drives.

In an environment where I do not have 100% control over what files are saved in what location , I will often have issues with various application owners creating ad-hoc BAK files . This develops into a disk - space issue , causing data and log files to have no more room to grow.

 I use the script below to search all the relevant servers and return a list of BAK files . I then send the report to the various application owners.

To run , copy and paste straight into a Powershell cmdlet ,which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension.

foreach($comp in (get-content C:\servers.txt))
{
     $comp
     # Powershell script to list the BAK files under the  folder
     #customise for your own purposes
     $Dir = get-childitem "E:\myfolder" -recurse
     # $Dir |get-member
     $List = $Dir | where {$_.extension -eq ".BAK"}
     $List |format-table name,length,lastwritetime,fullname > C:\bakfilesout.txt

}

Related Posts

Powershell Scripts for DBA

Powershell List all Patches Updates on a Server