Learning SMO & Powershell

7. April 2010 by Bennett.

I created a small script to collect SQL Server file utilization data into a repository.  I’m collecting the space used by the files as well as the space used internally by SQL Server.  I’m doing this across all production databases and servers, so the T-SQL fileproperty function was not all that useful since it only works against the current database.  The solution was to use SMO in a PowerShell script to collect the data since it’s very easy to iterate across multiple servers and databases.  I’ll write up the solution at a later date, but for now I wanted to mention a minor fact that I learned today.

The SMO Database class has a Status property which returns at least two values, “Normal” and “Offline”.  You can use it to avoid trying to get info for offline databases.  For example:

if ( $db.status -eq “Normal”) { #exclude offline databases
#do something

}

Posted in Powershell, SQL Server | Print | 1 Comment »