Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...Thank you. I have never said it, but you have a great web site. It is of immense help...."

Geography

Where in the world do Tek-Tips members come from?
lewatkin (Programmer)
29 Mar 11 12:46
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 (MIS)
15 Apr 11 9:37
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:

CODE

$files | select $db.name, Name, Filename, Size | Export-CSV -delimiter "|" -path "C:\DBFiles.txt" -notypeinformation
 
lewatkin (Programmer)
15 Apr 11 13:12
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 (MIS)
20 Apr 11 14:34
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: ConvertTo-HTML Help 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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close