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:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx 

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.

Lock

Permission

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

http://msdn.microsoft.com/enus/library/microsoft.sqlserver.management.smo.server.aspx

 When you create an object for “Server”.

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

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”}

 Enumerates

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

 Name

—-

EnumActiveCurrentSessionTraceFlags    :

EnumActiveGlobalTraceFlags                      : Traceflags

EnumAvailableMedia                                     : media

EnumCollations                                                 : Collation

EnumDatabaseMirrorWitnessRoles         : Mirror 

EnumDetachedDatabaseFiles                     : Detached

EnumDetachedLogFiles                                 : Detached log

EnumDirectories                             

EnumErrorLogs                                                 : Errorlog ()

EnumLocks                                                         : Current Locks

EnumMembers                               

EnumObjectPermissions                             

EnumPerformanceCounters                       : Performance counter

EnumProcesses                                                : Current Sys processes

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

 http://msdn.microsoft.com/enus/library/microsoft.sqlserver.management.smo.database.aspx

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.

$databases.Create()

 

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

$databases.Create()

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.

About these ads
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