SQL Server - Powershell for SQL SMO

 Powershell for SQL SMO has fantastic potential. Robert Matthew Cook left me some code on SQL Server xp_msver  - seeing if I was interested on expanding.

 The Server Object offers SQL Server Instance  Information

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$SmoServer = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server"
Export-Clixml -InputObject $SmoServer.Information -Path "$Env:userprofile\desktop\smoinformation.xml" -Depth 1 

The SQL Server health code I run on SQL Server inventories is based around Microsoft.SqlServer.Smo, which I’ll post in the future.

 A quick and easy working of the code is to iterate through a list of SQL Server instances , situated  on “instances.txt”

Place this code in a Powershell file , and with the “instances.txt” file in the same path , execute the Powershell script. Assuming security is sufficient , an xml output file is created with the Microsoft.SqlServer.Management.Smo.Server Information  PER instance.

There are many different ways to output the information – for example into a SQL Server database, Excel spreadsheet with multiple worksheets, html files etc.

 The output file in the example below – will be named “sql_server_info_2011-06-24T145531SERVER_INSTANCE.xml”

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$outputfile="\sql_server_info_" + $isodate 
 foreach ($svr in get-content "$basepath\instances.txt")
{

               $outputfilefull = $basepath + $outputfile + ($svr -replace("\\","_")) + ".xml"

                [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 

                $SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server "$svr"

                Export-Clixml -InputObject $SmoServer.Information -Path "$outputfilefull" -Depth 1

 }

A snippet of the xml file is :

- 
  Name=BuildNumber/Type=System.Int32/Writable=False/Value=4311

  Name=Edition/Type=System.String/Writable=False/Value=Standard Edition

  Name=ErrorLogPath/Type=System.String/Writable=False/Value=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

  Name=HasNullSaPassword/Type=System.Boolean/Writable=False/Value=null

  Name=IsCaseSensitive/Type=System.Boolean/Writable=False/Value=False

  Name=IsFullTextInstalled/Type=System.Boolean/Writable=False/Value=True

  Name=Language/Type=System.String/Writable=False/Value=English (United States)

  Name=MasterDBLogPath/Type=System.String/Writable=False/Value=E:\MSSQLSERVER\MSSQL$INSTANCE1\Data

  Name=MasterDBPath/Type=System.String/Writable=False/Value=E:\MSSQLSERVER\MSSQL$ INSTANCE1\Data

  Name=MaxPrecision/Type=System.Byte/Writable=False/Value=38

  Name=NetName/Type=System.String/Writable=False/Value=SERVER1

  Name=OSVersion/Type=System.String/Writable=False/Value=5.2 (3790)

  Name=PhysicalMemory/Type=System.Int32/Writable=False/Value=32767

  Name=Platform/Type=System.String/Writable=False/Value=NT INTEL X86

  Name=Processors/Type=System.Int32/Writable=False/Value=8

  Name=Product/Type=System.String/Writable=False/Value=Microsoft SQL Server

  Name=RootDirectory/Type=System.String/Writable=False/Value=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

  Name=VersionMajor/Type=System.Int32/Writable=False/Value=9

  Name=VersionMinor/Type=System.Int32/Writable=False/Value=0

  Name=VersionString/Type=System.String/Writable=False/Value=9.00.4311.00

  Name=Collation/Type=System.String/Writable=False/Value=Latin1_General_CI_AS

  Name=EngineEdition/Type=System.Int32/Writable=False/Value=2

  Name=IsClustered/Type=System.Boolean/Writable=False/Value=False

  Name=IsSingleUser/Type=System.Boolean/Writable=False/Value=False

  Name=ProductLevel/Type=System.String/Writable=False/Value=SP3

  Name=BuildClrVersionString/Type=System.String/Writable=False/Value=v2.0.50727

  Name=CollationID/Type=System.Int32/Writable=False/Value=53256

  Name=ComparisonStyle/Type=System.Int32/Writable=False/Value=196609

  Name=ComputerNamePhysicalNetBIOS/Type=System.String/Writable=False/Value=SERVER1

  Name=ResourceLastUpdateDateTime/Type=System.DateTime/Writable=False/Value=09/28/2010 14:41:23

  Name=ResourceVersionString/Type=System.String/Writable=False/Value=9.00.4311

  Name=SqlCharSet/Type=System.Int16/Writable=False/Value=1

  Name=SqlCharSetName/Type=System.String/Writable=False/Value=iso_1

  Name=SqlSortOrder/Type=System.Int16/Writable=False/Value=0

  Name=SqlSortOrderName/Type=System.String/Writable=False/Value=bin_ascii_8

  

Powershell for SQL SMO  Server Object can produce all sorts of useful information for the SQL Server DBA.

Related Posts

Powershell Scripts for DBA

Powershell - run script on all sql servers