SQL Server - Powershell and SQL BAK files

18 August,2008 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - Powershell and SQL BAK files


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer