by staggerlee011 at 2013-03-28 06:55:09
Hi all,by coderaven at 2013-03-28 07:15:58
i have list of sql servers, and want to find out the backup location and size of the folder. Ive managed to get all the info i want. but i cant see how to put it into a table?!
The code i have is:
$serverlist = get-content -path c:/serverlist.txt
$serverlist | ForEach-Object {
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $
$BackupDirectory = $s.Settings.BackupDirectory
$serverName = $s.name
$servername
$root = $BackupDirectory.Substring(0,1)
$folder = $BackupDirectory.Substring(3)
$pathname = "\" + $ + "" + $root + "$" + $folder
$pathname
$BackupSize = ( Get-ChildItem $pathname -Recurse -Force | Measure-Object -Property Length -Sum ).Sum / 1GB
$BackupSize
}
Id like to have csv at the end with the following tables
ServerName, BackupDirectory, Size
Which i have in there as the variables ($serverName, $pathname, $BackupSize), but im not sure how to put them together!?
Any ideas?
I would recommend using a PSObject. Something like this would work inside your loopby staggerlee011 at 2013-03-28 07:45:00$PSO = New-Object -Type PSObject
$PSO | Add-Member -MemberType NoteProperty -Name Server -Value $ServerName -PassThru |
Add-Member -MemberType NoteProperty -Name Path -Value $pathname -PassThru |
Add-Member -MemberType NoteProperty -Name Size -Value $BackupSize
$PSO
This works in PSv2 an v3 there are some new ways to do it and a lot of information out there if you just look for PSObject. Once you have it in this format, you can use Export-Csv to get it to file.
hmm i had alreayd posted a ty but it doesnt seem to have posted… so ill try again!
thanks for the help!
Fully working script :
$serverlist = get-content -path c:/serverlist.txt
$results = @()
$serverlist | ForEach-Object {
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $
$BackupDirectory = $s.Settings.BackupDirectory
$serverName = $s.name
##$servername
$root = $BackupDirectory.Substring(0,1)
$folder = $BackupDirectory.Substring(3)
$pathname = "\" + $ + "" + $root + "$" + $folder
##$pathname
$BackupSize = ( Get-ChildItem $pathname -Recurse -Force | Measure-Object -Property Length -Sum ).Sum / 1GB
##$BackupSize
$PSO = New-Object -Type PSObject
$PSO | Add-Member -MemberType NoteProperty -Name Server -Value $ServerName -PassThru |
Add-Member -MemberType NoteProperty -Name Path -Value $pathname -PassThru |
Add-Member -MemberType NoteProperty -Name Size -Value $BackupSize
$results += $PSO
}
$results | export-csv -path c:\summary23.csv