Sum of file folder size based on file/folder name

2019-09-07 06:59发布

问题:

I have multiple folders across a number of SQL Servers that contain hundreds/thousands of databases. Each database comprises of three elements:

<dbname>.MDF
<dbname>.LDF
<dbname>files (Folder that contains db files/attachments)

I need to marry these files together and add up their total size, does anyone have any advice on how to do this?

EDIT : Just to clarify, I'm currently able to output the filesizes of the MDF/LDF files, I have a separate script that summarises the folder sizes. I need a method of adding together a .MDF/.LDF/DBFiles folder when their name matches. Bearing in mind all of the files are prefixed with the database name.

EDIT #2: The 2 options given so far sum together the .mdf/.ldf files with no problem, but do not add the folder size of the DBFiles folder. Does anyone have any input on how to amend these scripts to include a folder beginning with the same name.

First provided script:

$root = 'C:\db\folder'
Get-ChildItem "$root\*.mdf" | Select-Object -Expand BaseName |
ForEach-Object {
New-Object -Type PSObject -Property @{
  Database = $_
  Size     = Get-ChildItem "$root\$_*" -Recurse |
             Measure-Object Length -Sum |
             Select-Object -Expand Sum
}
}

Second provided script:

gci "c:\temp" -file -Include "*.mdf", "*.ldf" -Recurse | 
group BaseName, DirectoryName  | 
%{new-object psobject -Property @{FilesAndPath=$_.Name; Size=($_.Group | gci |  Measure-Object Length -Sum).Sum }    }

EDIT #3:

Thanks to Ansgar (below), the updated solution has done the trick perfectly. Updating question with final solution:

$root = 'C:\db\folder'
Get-ChildItem "$root\*.mdf" | Select-Object -Expand BaseName |
  ForEach-Object {
    New-Object -Type PSObject -Property @{
      Database = $_
      Size     = Get-ChildItem "$root\$_*\*" -Recurse |
                 Measure-Object Length -Sum |
                 Select-Object -Expand Sum
    }
  }

回答1:

Enumerate just the .mdf files from your database folder, then enumerate the files and folders for each basename.

$root = 'C:\db\folder'
Get-ChildItem "$root\*.mdf" | Select-Object -Expand BaseName |
  ForEach-Object {
    New-Object -Type PSObject -Property @{
      Database = $_
      Size     = Get-ChildItem "$root\$_*\*" -Recurse |
                 Measure-Object Length -Sum |
                 Select-Object -Expand Sum
    }
  }


回答2:

if you want the sum of sise files database by dir and name file (without extension), try it

gci "c:\temp" -file -Include "*.mdf", "*.ldf" -Recurse | 
group BaseName, DirectoryName  | 
%{new-object psobject -Property @{FilesAndPath=$_.Name; Size=($_.Group | gci | Measure-Object Length -Sum).Sum }    }

Modifiy a little the include gci if necessary