Inventorying your SQL Servers with Powershell and SMO: Part 2

In the first part of this series we looked at using PowerShell and SMO to inventory your SQL Servers at the instance level.

The Introduction and Part 1 cover the SMO in more detail. If you have not read them yet please do so before continuing with this article.

In a database we called smoInventory, we created a table called smoInstances.

We obtained the information we wanted via PowerShell by interacting with the SMO, and then we pushed that data into SQL Server to store it.

If we think of the SQL Server SMO as a hierarchy, we have only thus far collected data at the top level – which I’ll call the Instance Level.

In this demo, I’m going to show you how simple it is to drop down a level, and we’re going to effectively drill into the instance and collect data at the Database level.

Step 1: Prepare the SQL Server Table

We’re going to be collecting some generic, but very useful and fairly extensive information about databases in our list of instances.

From the SMO we’re going to pull the following properties:

  • Instance Name
  • Database Name
  • Status
  • Owner
  • Recovery Model
  • Date Created
  • Last Backup
  • Last Log Backup
  • Compatibility Level

There are of course many more properties available in the Database collection. In fact, we can explore them with the following in PowerShell:

$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $svr.databases

$dbs | Get-Member

So let’s create the table that we’ll call smoDatabases in our smoInventory database.

CREATE TABLE dbo.smoDatabases(
FullInstanceName varchar(500) NULL,
DatabaseName varchar(500) NULL,
DatabaseStatus varchar(500) NULL,
DatabaseCollation varchar(500) NULL,
DatabaseOwner varchar(500) NULL,
DatabaseRecovery varchar(25) NULL,
DatabaseCreate datetime NULL,
DatabaseLastBU varchar(500) NULL,
DatabaseLastLogBU varchar(500) NULL,
DatabaseCompatLevel varchar(50) NULL

Step 2: Do the PowerShell Bit…

We covered this in Part 1, but essentially, we need to initialise the SMO again, and populate an array of our SQL Servers from our inst_list table, which we will then iterate over.

Import-Module sqlps -DisableNameChecking

$instances = @(Invoke-Sqlcmd -serverinstance uos-t-sqli-09\tde0101 -database smoInventory -query "select name from inst_list") | select-object -expand name

foreach ($instance in $instances)


We now need to access the Databases collection of the SMO. We’ll do that simply by creating a variable $dbs and setting that to $svr.Databases.

$dbs = $svr.databases

We’ve already got a foreach block to deal with each instance. We now need a second foreach block to deal with each database in each instance.

foreach ($db in $dbs)

Set the variables:

$dbinstance = $db.Parent
$dbname = $db.Name
$dbstatus = $db.Status
$dbcollation = $db.Collation
$dbowner = $db.Owner
$dbrecov = $db.RecoveryModel
$dbcreate = $db.CreateDate
$dblastbu = $db.LastBackupDate
$dblastlog = $db.LastLogBackupDate
$dbcompat = $db.CompatibilityLevel

Insert the data into our table and close off the foreach blocks

Invoke-Sqlcmd -serverinstance sqlbuffetserver\instance1 -database smoInventory -Query `
"INSERT INTO smoDatabases VALUES (`
CASE WHEN '$dbcreate' = '01/01/0001 00:00:00' THEN NULL ELSE '$dbcreate' END,`
CASE WHEN '$dblastbu' = '01/01/0001 00:00:00' THEN NULL ELSE '$dblastbu' END,`
CASE WHEN '$dblastlog' = '01/01/0001 00:00:00' THEN NULL ELSE '$dblastlog' END,`

Simply put, for each database in each instance we’re going to insert a row of properties (the ones we defined above) into our smoDatabases table.

We now have two tables in our database. smoInstances and smoDatabases. This gives us our first one-to-many relationship and the beginning of an awesome, extensive and really useful SQL Server inventory.

In the next part, we’ll look at digging a little deeper into the SMO by looking at Security objects. We’ll also look at using methods as well as properties to get important information.


One thought on “Inventorying your SQL Servers with Powershell and SMO: Part 2

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s