PowerShell for Backup Planning

To size a backup system, you need to decide on a few key pieces of information: retention range, amount in GB of file data to be backed up & change rate on that data. For applications, you will need to know SQL database sizes and change rates. Same for Exchange.

luckily with powershell, it's quick and easy. I'm a lazy scripter so I'm sure this script could be much improved, but it worked for my purposes.

to send server name, volume name, volume size, and free space to a .csv file:

$computer="server1","server2","server3"
$credential=get-credential
ForEach ($item in $computer)
{
$disk = get-wmiobject -credential $credential -query "select * from win32_logicaldisk where DriveType='3'" -computername $item
foreach($drive in $disk)
{
$item + "," + $drive.Name + "," + [int]($drive.size/1gb) +"," +[int]($drive.freespace/1gb)|out-file test.csv -append
}
}

This script will ask you to login with administrator credentials, then output information for all local hard drives (no removable drives or CD-ROMs), and output it to a .csv file.

For clusters, it will only retrieve the primary member's information.

What about SQL? We can simply search for .mdf files and list their sizes. We could also use this same bit to search for LDF files or any other file type. This will output to a .csv file the Server name, the path of the .mdf file, and the file size.

$computer="Server1","Server2","Server3"
$credential=get-credential
foreach ($svr in $computer)
{$dbFiles = Get-WmiObject -credential $credential -Class CIM_DataFile -Filter "Extension = 'mdf'" -ComputerName $svr;
$dbFiles | ForEach-Object { ($_.csname) + "," + ($_.Name) + "," + ($_.filesize/1gb)}| out-file testsql.csv -append

}

Notice that both scripts loop through an array to find data. You could also use an active directory query to populate the array instead of entering the names manually.

by the way, I love how I can convert bytes to GB just by dividing by "1GB" - powershell has built in constants for translating file sizes. You can also divide by MB or KB.