Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - Powershell for SQL SMO

17 June,2011 by Jack Vamvas

 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”

Instances 

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer