PowerSQL – Find Size, Name, LastAccess and Last Modified time of all bak files across ALL Servers

We came across a scenario where database bak files being created from long time which are no more in use and hence cleanup those files since we’ve backup tool is in place. We’ve many *.BAK (database backup files) created across ‘N’ number of servers. We have decided to cleanup all *.bak files .  First we’ve listed all *.bak files  and validated it once and make sure which one is eligible for deletion. In this post, I’m just doing information gathering. To get a remote drive location, I’ve referred GetUNCpath function which is already posted in one of my previous blog.

The below code has three parts
1) Get the UNC path of each drive of a any server – get remote drive location
2) Test-Connection with the server – check for successful connection or not
3) Browse through each drive – Recursive search

Note:-

1)Input file =List.txt
2)Input Parameter = Extension (Any file extension BAK,SQL,PS1 etc:- In this case its BAK)
3)OutputFile =FileList.txt

The Server names are included in list.txt
aqvd002612
aqdb1812

*************************************

FUNCTION getUNCPath($infile)
{
$qualifier = Split-Path $infile -qualifier
$drive = $qualifier.substring(0,1)
$noqualifier = Split-Path $infile -noQualifier
“$drive`$$noqualifier”
}

FUNCTION Get-Files
{
Param([String]$extn)
FOREACH ($server in Get-Content List.txt)
{
if (Test-Connection -ComputerName $Server -Count 2 -Quiet )
{
$dp = Get-WmiObject win32_logicaldisk -ComputerName $server | Where-Object {$_.drivetype -eq 3}|select deviceid
foreach ($item in $dp)
{
$UNC=getUNCPath($item.deviceid)
$Path=”\\$Server\$UNC”
Write-host $path
Get-childitem $path -RECURSE –FORCE -ERRORACTION silentlycontinue *.$extn |select-object Name,DirectoryName,@{Name=”Size(MB)”;Expression={[math]::round($_.Length/1024/1024,3)}}, CreationTime,LastAccessTime,LastWriteTime |out-file -append c:\FileList.txt
}
}
else
{
Write-Warning “$Server seems dead not pinging”
}
}
}

**************************************************

The Function calling code is given below

PS C:\> Get-Files -extn bak

Output – FileList.txt

Name : MMetaService_3-27-2013.bak
DirectoryName : \\aqdb1812\C$
Size(MB) : 5.156
CreationTime : 3/27/2013 10:37:26 PM
LastAccessTime : 4/4/2013 10:26:12 AM
LastWriteTime : 3/27/2013 10:37:26 PM

Name : WSS_Content_3-5-2013.bak
DirectoryName : \\aqdb1812\C$
Size(MB) : 161.166
CreationTime : 3/15/2013 2:08:25 PM
LastAccessTime : 4/29/2013 2:03:31 PM
LastWriteTime : 3/16/2013 1:59:26 AM

Invalid ServerName in List.txt

PS C:\> Get-Files -extn bak
WARNING: aqvd002612 seems dead not pinging

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
Aside | This entry was posted in PowerShell, SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s