I have 2 Remote SQL 2000 servers that I want to get the following config info from:

• All running DB names
  
• DB filename/path
• Log File name/path
• DB/Log size
• Date Last backed up

I have installed SQL 2008 Management Studio on my Powershell machine.

Is there a way to get this info remotely from SQL 2000 servers with Powershell?   If not,  is this available remotely via SQL 2005/2008?

Thanks,
-MattG

You could use SQL Powershell Extensions (SQLPSX): http://www.codeplex.com/sqlpsx

$dbs = Get-SqlDatabase Z002
$dbs | Select Select Name, LastBackupDate
$dbs | Get-SqlDataFile
$dbs | Get-SqlLogFile

The call to Get-SqlDatabase will return a collection of all databases other than system databases for the server named Z002, the next three lines return the information you are requesting.

There are several of ways of getting this information in addition to using SQLPSX. You can issue T-SQL calls from Powershell, use the SMO classes directly, or use the SQL Server 2008 Provider to get the same information. If you'd like to use an alternative approach let me know and I'll post the code.

I have written my scripts generically enough to go against both 2000 and 2005 since I have both in several of my environments.  I have written one that loops through a text file that lists all the instances I want it to check.  Then here's a piece that grabs the number of databases and last backup date. 

param
(
  [string] $filename
)
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$computers = get-content $filename
foreach ($computer in $computers)
{$con = "server=$computer;database=master;Integrated Security=sspi"
  $cmdd = "SELECT COUNT(*) from sys.databases"
  $db = new-object System.Data.SqlClient.SqlDataAdapter ($cmdd, $con)
  $du = new-object System.Data.DataTable
  $db.fill($du) | out-null
$value2 = $du.Rows[0][0]
write-host "Number of databases: " $value2
write-host "-----------------------------------"
write-host " "
write-host "Databases and Last Backup Info: "
write-host "-----------------------------------"
write-host " "
$Server.Databases | Select-Object @{Name = '$computer';Expression = {$Server.name}}, name, lastbackupdate
}

This is good use of Powershell, one suggestion

You could get rid of your select count query by using the cmdlet measure-object to return number of databases:

$measure = $Server.Databases | measure-object
"Number of database: $($measure.count)"