http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/02/09/a-month-of-powershell-day-9-getting-started-with-smo/

Printed 2014/12/21 01:58AM

A Month of PowerShell – Day 9 (Getting Started with SMO)

By Wayne Sheffield, 2013/02/09

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.

  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.
  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

Using SMO with PowerShell

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

Name Description
SQL Accesses the Database Engine, Agent, DB Mail, Service Broker Objects
SQLPolicy Accesses Policy-Based Management Objects
SQLRegistration Accesses Registered Servers and Central Management Server
DataCollection Accesses the Data Collection feature from Management Data Warehouse
Utility Managed objects (Database Engine instances)
DAC Data Application Objects (NOT Dedicated Admin Connection)

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

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.