Powershell Day 15

Day 15 Introduction to Powershell for MS Sql Server

So far we have discussed several features and information about Windows Powershell, how it works and how powerful it is. I am impressed the way it is handling the stuff… you must be feeling the same right? Okay,  Now onwards I will be discussing Powershell with Microsoft Sql Server (I will refer Sql or Sql Server),  as we are almost half our time reach of our A Day with Powershell series (April Month) discussing about windows – which was very important information and necessary to know Powershell.

 As we discussed that with Powershell we can do one thing with different ways with the help of several components. With Powershell we can implement almost everything. Powershell is Shell +Scripting Language. Following are some of the components /members of Powershell.

  •  Cmdlets
  • .Net Framework
  •  COM
  • WMI
  • Snapins

 I am as a Sql Server Developer/DBA and Powershell is very important for Sql server developer/DBA as well. Powershell is develop completely on .Net Framework. So everything of .net component can be used here.  As a scripting with Powershell we can script something and implement the same at different location at any number of time without any error ,which is not easy with any GUI.

Sql server is a very powerful RDBMS these days. And used for enterprise platform and capable of handling big mission critical system.  With sql server 2008 Powershell 2 is build it. No need to install it explicitly(same for Windows 2008 onwards). At sql server 2008 onwards we can run the Powershell script through Sql server Agent job. Which can be scheduled… cool.

Compatibility:  Powershell is developed in late 2006, and used mostly for windows administration. It was so powerful that it can also incorporated in sql server 2008. But it can able to handle the instances of sql server 2000 onwards – with features they support. But as it is introduced in sql server 2008 you must have to use Sql server 2008 client version to access earlier version of sql server

Why Powershell for Sql Server:-

  1. Error Free implementation of task from one system to another (Migration).
  2. With scripting, easy to implement and light weighted (no GUI)
  3. Script can be use for multiple locations and can be use for future reference.
  4. Integrate Windows programming and Sql server Coding efficiently to achieve the any requirement(if any) – (Processes/Services/Registry/ADSI/Perfmon/Eventvwr)
  5. Future Centralized Language for Windows programming – includes Applications/Hardware.
  6. It’s build in  with Sql server 2008(or Windows 2008) onwards- means it’s going play a very strong role in development –Mini shell.
  7. It’s very easy to learn and expandable (snapins) as per our requirement.
  8. Growing very rapidly with snapins from different big companies like (quest/Idera) including features like intellisence and ADSI plugins.
  9. Scripting has been a powerful tool for Unix administrators for a long time.  Windows administrators have had fewer and less capable options for automating administrative processes until the introduction of PowerShell. 


okay. Here you go. What do we do to open Powershell, 



are we are done…. We can work with Powershell Script.

To work with Sql server there are several ways.

  1. 1.     SQLPS – Mini Shell:

Can also open it using management studio, select any object and right click –will have an option to “Start Powershell” which will open the sqlps at directory location which you select.

Sqlserver CmdLets

  • Invoke-Sqlcmd
  • Invoke-PolicyEvaluation
  • Encode-SqlName
  • Decode-SqlName
  • Convert-UrnToPath

No snapins are allowed

  1. 2.     . Net Assembly.



To access the sql server load the .net Assembly

SMO and ADO.net

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management.


[system.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.sqlserver.smo’) |out-null

Some of the smo’s are as follows








And once you registered the Assembly you can create an object to access sql server with that object.


Ps C:\>[system.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.sqlserver.smo’) |out-null

Ps C:\>$server=new-object (‘Microsoft.sqlserver.management.smo.server’) MSSqlSqlserver

Here Sqlserver is the default instance you can specify the sql instance could be named instance [hostname\instnacename]

You are done. You can use the $server object to retrieve the sql related command.

To get the member of the object $server

Ps C:\>


Snap is is like adding external dll /objects /cmdlets to programming to extent the existing functionality for easy to use.


Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

4.Use SqlAgent job to run Powershell

We can use sql agent job’s steps to run the Powershell script.



$m = New-Object (‘Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer’)

Use WMI object to integrate sql server with wmi.


We will discuss more in detail on each of these in our next blogs.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.