Welcome to Day 9 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

This blog series is about PowerShell, and this blog is about SQL Server, so it’s about time we started integrating the two and doing some SQL Server things with PowerShell.

SMO

It’s hard to find a better description of what SQL Server Management Objects (SMO) are than how Microsoft describes them at http://msdn.microsoft.com/en-us/library/ms162169.aspx: “SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.” (The start of the SMO documentation is at http://msdn.microsoft.com/en-us/library/hh248032.aspx.) The SMO object model diagram is available at http://msdn.microsoft.com/en-us/library/ms162209.aspx. In SQL Server 2012, you need to download two additional files to use the SQL Server 2012 provider for PowerShell from the Microsoft Download site:

1. Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012
2. Microsoft SQL Server 2012 Shared Management Objects

Ensure that you download and install the appropriate edition (X86 / X64) for your client.

• SMO was introduced in SQL Server 2005, and it extends and replaces SQL-DMO (Distributed Management Objects), which was utilized in SQL Server 7.0 and 2000.
• SMO is implemented as a .NET Framework assembly (vs. COM for SQL-DMO).

1. The default location of the assemblies is at C:\Program Files\Microsoft SQL Server\<version>\SDK\Assemblies
2. The Microsoft .NET Framework 2.0 (SQL 2012; 1.0 for 2008/2008R2) must be installed prior to using SMO objects.

• Includes instance classes and utility classes

1. Instance classes represent SQL Server objects (servers, databases, tables, triggers, stored procedures, etc.)
2. Utility classes perform certain tasks

i.      Transfer class

ii.      Backup and Restore classes

iii.      Scripter Class

• The ServerConnection class is utilized to connect to the SQL instance and to perform operations on it through SMO.
• SSMS uses SMO to perform all actions, so if you use SSMS then you are already using SMO, albeit indirectly. Now we just need to learn how to directly use SMO.

Using SMO with PowerShell

When Microsoft integrated PowerShell with SQL Server 2008, they created a SQL Server Mini-Shell – SQLPS.exe.

• The Mini-Shell is started by right-clicking on an object in SSMS and selecting “Start PowerShell”.
• When running the Mini-Shell, its default Execution Policy is RemoteSigned.
• When running the Mini-Shell, the SqlServerCmdletSnapin and SqlServerProviderSnapin are automatically loaded
  • These should be added to your profile for running PowerShell outside of the Mini-Shell.
  • Add to your profile “Import-Module “sqlps” –DisableNameChecking”
  • These snapins provide a new PSDrive – SQLSERVER. Running Get-ChildItem SQLSERVER: | Select-Object Name will return this list of directories available, which are the major entry points into SQL Server:

Note that the Import-Module “sqlps” is for loading the SQL Server 2012 libraries. Prior to SQL Server 2012, the following code is commonly used to load the appropriate SQL libraries into PowerShell:

Source code

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
  }

It’s time for SQL things… First of all, let’s get a list of all of the SQL instances on this server, along with the Service Account, OS Platform, Version, ProductLevel and Edition. We’ll use the PSDrive SQLSERVER to accomplish this:

Source code

Get-ChildItem SQLSERVER:\SQL\LocalHost | `
    Select-Object InstanceName, ServiceAccount, Platform, Version, ProductLevel, Edition |`
    Format-Table -AutoSize

How would you like to get a list of all SQL Servers on your network – even if they are shut down? Since PowerShell works within .NET, you can call methods on .NET classes. For instance, this one command will get all of the SQL Servers on your network (say good-bye to SQLPing!):

Source code

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Now let’s get a list of all databases on an instance, along with whether AutoShrink is enabled, when the last backup was performed, what the page verify option is, and the recovery model. These two examples both produce the same results:

Source code

$server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') ".\SQL2012"
$server.Databases | `
    Select-Object Name, AutoShrink, LastBackupDate, PageVerify, RecoveryModel | `
    Format-Table –AutoSize

Or:

Source code

Get-ChildItem SQLServer:\SQL\localhost\SQL2012\Databases | `
    Select-Object Name, AutoShrink, LastBackupDate, PageVerify, RecoveryModel | `
    Format-Table -AutoSize

There are a lot of properties that show up here… way more than what you see if you were to perform a SELECT * FROM sys.databases. You can see all of the properties by running (after adjusting the Server\Instance name for your environment – note that if you are using a default instance, you need to use the keyword DEFAULT as the instance name):

Source code

Get-ChildItem SQLServer:\SQL\localhost\SQL2012\Databases

Let’s start using some of the power of PowerShell to do other tasks. Let’s automate checking all of the databases in multiple servers with the above command. We’ll start off with creating a file of servers (or you can use the file created earlier). Run this command to open up notepad, creating a new file in your temp directory:

Source code

notepad $ENV:TEMP\ServerList.csv

On the first line, enter the Column Name “ServerName”. On each line below that, enter some SQL Server instances that you have access to and save the file. If any of these instances are default instances, use “\DEFAULT” after the server name. For instance, my file looks like:

Source code

ServerName
localhost\SQL2005
localhost\SQL2008
localhost\SQL2008R2
localhost\SQL2012

What we want to do is to read this file, and for each server retrieve the database information that we just ran. This is performed by:

Source code

$Servers = Import-CSV $ENV:TEMP\ServerList.csv
ForEach ($Server in $Servers)
{
  $Server = $Server.ServerName
  Get-ChildItem SQLServer:\SQL\"$Server"\Databases |`
  Select-Object Parent, Name, AutoShrink, LastBackupDate, PageVerify, RecoveryModel |`
  Format-Table -AutoSize
}

Here I’ve added the Parent property, to return which server the result set is for. However, this produces a result set for each of the servers in my file. What I’d like is to put these all into one result set:

Source code

$MyResults = @() #build an empty array
$Servers = Import-CSV $ENV:TEMP\ServerList.csv
ForEach ($Server in $Servers)
{
    $Server = $Server.ServerName
    $MyResults += ` #Add to the array all of the database info from each server
    Get-ChildItem SQLServer:\SQL\"$Server"\Databases |`
    Select-Object Parent, Name, AutoShrink, LastBackupDate, PageVerify, RecoveryModel
}
$MyResults | Format-Table -AutoSize #display the array

Here we start off by making an empty array. Then inside the ForEach loop, we add the results of the current server to the array. Finally, we display the results. Not bad for just two extra lines of code.

Now we have the start of being able to automate connecting to servers across the network. In fact, as long as you have access, this will get the information from any server on your network.

Running SQL Statements

Sometimes, you will want to run a SQL statement. For this, you would use the invoke-sqlcmd cmdlet:

Source code

$Query = @'
SELECT  name, size_mb = size/128.0
FROM    sys.master_files
'@
 
invoke-sqlcmd -ServerInstance 'localhost\SQL2012' `
    -Database 'master' -Query $Query |`
    Format-Table -AutoSize