Powershell Day 21

Day 21 SMO programming Part 2 

Continue with my earlier blog where I discussed about the information about SMO. Today we will talk about programming sql server using SMO. 

SMO is a very big subject to discuss and each method /property can be discussed detail and I can have a complete Book on it, so I will try to focus major stuff here and rest you can explore by your own.

For All Classes:


SERVER Class: Microsoft.SqlServer.Management.Smo.Server:

Server class has selever properties and methods used to work on sql server properties. Its not easy to explain all here, but will try to make a special blog for some special methods like performance monitor.

 Server class is used to manage server configuration, we can get and set the parameter/values to the sql server using server class.

 Can go to database and get the database properties/methods.

Get OS/Sql version

Get service related values

Work on  sql processes.



Error logs


All Server related task can be performed.

$Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘server\instance’

Complete list and description of server class – property and methods are here


 When you create an object for “Server”.

 PS C:\> $smo1 |gm | ? {$_.Name -like “*version*” -and $_.MemberType -eq “Property”} | select name



BuildClrVersion              :CLR version

BuildClrVersionString      : CLR version String -FULL

OSVersion                     :OS Version…

ResourceVersion            : Resource Database Version

ResourceVersionString   : Resource Database Version String -FULL

Version                         :  Sql Version -Full

VersionMajor                : Major –Version 9/10/10.5

VersionMinor                : Minor – Service PAck

VersionString                 : Version FULL

BuildNumber                   : # eg 1600

 #just like sys.databases

PS C:\> $server1.databases |select name,status,recoverymodel,createdate

 #want to know the specific database related property/method, create another variable with $_name with that database

PS C:\> $DB_srv1=$server1.databases | ? {$_.name –eq “DBNAME”}


PS C:\> $smo1 |gm | ? { $_.MemberType -eq “method” -and $_.name -like “enum*”} |select name



EnumActiveCurrentSessionTraceFlags    :

EnumActiveGlobalTraceFlags                      : Traceflags

EnumAvailableMedia                                     : media

EnumCollations                                                 : Collation

EnumDatabaseMirrorWitnessRoles         : Mirror 

EnumDetachedDatabaseFiles                     : Detached

EnumDetachedLogFiles                                 : Detached log


EnumErrorLogs                                                 : Errorlog ()

EnumLocks                                                         : Current Locks



EnumPerformanceCounters                       : Performance counter

EnumProcesses                                                : Current Sys processes

 Database Class: Microsoft.SqlServer.Management.Smo.database:


As “Server” class handles server related task, “Database” Class is on database related and can manuputed the database included

>>Create database

>>Execute Any  query on database

>> Alter database

>>remove database

Database object is depends on server object, so before database object you should have server object created,

$Database1=new-object (‘Microsoft.SqlServer.Management.Smo.Database’) ($Server1,”databaseNm”)

 Here we will work on server $server1.

 #To create a database. –this will create a database with default configuration –model database.



To configure the database with file management you have to create several objects related to it. Like

                $FileGroup1 = New-Object (’Microsoft.SqlServer.Management.Smo.FileGroup) ($Database1,”FilegroupNm”)

                DataFile’= New-Object (’Microsoft.SqlServer.Management.Smo.DataFile’)($Databases1,”DataFilenm”)

                LogFile’= New-Object (’Microsoft.SqlServer.Management.Smo.LogFile’)($Databases1,”Logfilenm”

 And provide the appropriate parameter values to it. And at the and call function


This will create database base with above configuration values.

 Also we can set the recovery model As:

PS C:\> $database1.set_recoverymodel(1)

1 =FULL, 2 = Bulked log and 3 =Simple

 Finally, if you want to run any query on the database.

                PS C:\> $q1= $database.executequerywithresult(“Sp_help”)

 PS C:\> $q1.tables |ft 

#this will give query “Sp_help” output

As usual to get complete list of properties and methods use get-member and explore the database class

 There is so much to do that will try to try cover in next blog.

This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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