Pulling Information from SQL Server with Powershell, for Documentation Purposes

I will preface this by saying that I'm an accidental DBA, and probably not a very good one. I managed to get transaction log backups working properly, which I posted about last week, but I'm all the time discovering things that I didn't know that I didn't know.

I have a script that I run once a week just to keep current documentation on my SQL servers. To work with a SQL server in Powershell (and my SQL Servers are SQL 2008R2 running on top of Windows 2008R2), you first need to have SQL Server Management Studio installed. This makes the Powershell modules available to you. After that, open a Powershell prompt and load the modules like so:

add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

Next, set up the database connection:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$SQLConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "MySQLServerName"

Now, you can explore the TONS of information there is by doing this:
$SQLConnection | get-member

There's a lot to probe in this list. Every item with a "Property" membertype yields information. Usually, I like to capture lists like this so I can go through the properties one at a time to find useful information. I would do this like so:
$SQLConnection | Get-Member | where-object {$_.Membertype -like "Property"} | select-object Name | out-file c:\temp\SQL_Properties.txt

Let's break this down a bit:

• $SQLConnection ***Query the information you go from the SQL Server.
• Get-Member ***List all properties and methods available on $SQLConnection.
• where-object {$_.Membertype -like "Property"} ***Only give me results for items that have "Property" as their membertype.
• select-object Name, Membertype ***I only want the name returned. 
• out-file c:\temp\SQL_Properties.txt ***Send the output to this file.

Now, let's look at just one of those properties: Databases

Running the following command will give you a lot of information regarding each database on the SQL Server:

$SQLConnection.databases

There's a lot to digest, especially if you have more than one database on there. Using the technique above, I'll break that down to include the pieces I want, like so:

$SQLConnection.databases | Get-Member | where-object {$_.Membertype -like "Property"} | select-object Name | out-file c:\temp\SQL_Database_Properties.txt

Let's just get a couple of interesting properties:

• Name
• Owner
• RecoveryModel
• Size
• LastBackupDate
• LastLogBackupDate
• SpaceAvailable
• CompatibilityLevel

$SQLConnection.databases | select Name, Owner, RecoveryModel, Size, LastBackupDate, LastLogBackupDate, SpaceAvailable, CompatibilityLevel

Now that you've got the hang of this, you can pick what you need to know and have it mailed to you via the send-mailmessage command or saved to a file with out-file/export-csv. Automate it, and you'll always have up-to-date documentation!