Powershell script

[lewatkin]

The following powershell script works fine....

foreach ($svr in get-content "C:\AllServers.txt")
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$serverinstance = new-object ('Microsoft.SqlServer.Management.Smo.Server') $svr

foreach ($db in $serverinstance.databases)
{
$fgs = $db.Filegroups
foreach ($fg in $fgs)
{
$files = $fg.files
$serverinstance.name + '|' `
+ $db.name + '|' `
+ $fg.name + '|' `
+ $fg.Filename + '|' `
+ $fg.size | out-file "C:\DBFiles.txt" -append
}
}
}

EXCEPT, the $fg.Filename will not output the .mdf filepath. It is empty.

When I change the script to.....

foreach ($svr in get-content "C:\AllServers.txt")
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$serverinstance = new-object ('Microsoft.SqlServer.Management.Smo.Server') $svr

foreach ($db in $serverinstance.databases)
{
$fgs = $db.Filegroups
foreach ($fg in $fgs)
{
$files = $fg.files
$files | select $db.name, Name, Filename, Size | out-file "C:\DBFiles.txt" -append
}
}
}
...it returns the file path perfectly.

Can someone please point me in the right direction? I need the above information output into a pipe delimited text file.

ugh....

Thanks yall!

 

[crobin1]

The Export-CSV cmdlet has a -Delimiter parameter which allows you to specify delimiters other than the comma. You should be able to do this in your second script:

$files | select $db.name, Name, Filename, Size | Export-CSV -delimiter "|" -path "C:\DBFiles.txt" -notypeinformation

 

[lewatkin]

Thank you for the reply and it is further than I was for sure. But its not quite what I was looking for. First, this only exports the information from the last server in the C:\AllServers.txt file. Additionally, the information is double quoted and pipe delimited (ex: |"dbname"|"E:\MSSQL\TimeAttend\xt3.mdf"|"569344")

I do appreciate your help though.

 

[crobin1]

I think the double-quotes are part of the output from Export-CSV, probably to avoid issues with commas within fields.

Probably your better bet is going to be to store the information in an array, and then output the array at the end of the script. You can see thread1619-1637606 for one example. That creates an array of objects. With your format requirement for the output file, it's probably simpler to build the output line as a string and add each string to the array.