powershell_series_new1 by yaofenjin

VIEWS: 272 PAGES: 87

									                                                                             Powershell a day –April 2011


acknowledgments:
Sorry about my English. I just write what I feel.

I am glad that Finally I could able to complete this April months a Day with Powershell. It was a
wonderful experience writing a blog per day. It’s not so easy to write a blog specially when you are
not much comfortable with writing and subject is new to you. To be honest I worked very hard on it.
And so time I have to spend almost 8 to 10 hrs a day to complete a blog. You will be surprise I started
writing on this April months blog from mid march but towards the end it was getting tough for me. As
I was learning the subject this course.

I highly recommend for one who is interested to master in the subject and want. You should try this at
least once a year as Glenn who has written this year as well. I will also try to make it an habit. I was so
dedicated from last 40/45 days on this series. And today I am very happy to have happy signoff from
it. I would like to thank everyone in the community via Googling I got so much help. Great help by
MSDN site and all forum/communities and authors/blogger - Ed Wilson, Bruce Payette, MAK, Yan,
Frank Koch, Lee Holmes, Jeff Hicks, Allen white, Aaron Nelson and those who has contributed on
internet for sharing great information.

last but not least my family and friends to whom I have not given much time –specially my daughter.
There is still so much to write, hope I will do it in my next blogs.

Finally… Thank you All readers.

Vinay.




1|Page
                                                               Powershell a day –April 2011


Table of Contents:

Powershell Day 1 - Introduction

Powershell Day 2 - Introduction Windows powershell

Powershell Day 3 - Scripting

Powershell Day 4 - Scripting

Powershell Day 5 - Scripting (condition/loops)

Powershell Day 6 - Functions & Error Handling

Powershell Day 7 - Security

Powershell Day 8 - eventlog

Powershell Day 9 - Process and services

Powershell Day 10 - Directory and Registry

Powershell Day 11 - COM and WMI

Powershell Day 12 - ADSI

Powershell Day 13 - Remoting & Tools, e-mail

Powershell Day 14 -Tools and more

Powershell Day 15 - Introcutin to Powershell with Sql server

Powershell Day 16 - SqlPS

Powershell Day 17 - Advance SqlPS

Powershell Day 18 - Snapin

Powershell Day 19 - Sqlserver with .Net

Powershell Day 20 - Smo

Powershell Day 21 - Smo 2 server database

Powershell Day 22 - Smo 3 --table column

Powershell Day 23 - Smo 4 -bkp restore

Powershell Day 24 - Performance Monitor
2|Page
                                           Powershell a day –April 2011


Powershell Day 25 - RMO

Powershell Day 26 - Generate script

Powershell Day 27 - Inventory

Powershell Day 28 - SqlPolicy

Powershell Day 29 – Advance security

Powershell Day 30 - Best Practice      -

Other a Month Blogs

References




3|Page
                                                                               Powershell a day –April 2011




                                 Day 1 Introduction to Powershell
I got inspired by great people like Paul Randal and decided to write a blog for a month about Powershell,
I am learning Powershell, lets see.

Powershell is the new command-line/scripting environment. The purpose of this is to automate the
routine for Microsoft windows any task.

When Microsoft was increasing in enterprise and need to go beyond GUI for enterprise platform for that
it require similar programming like UNIX shell programming. As .net and C# are GUI programming which
is more of user interface not command-line friendly and existing windows command line was not
enough, so Powershell is build. Jeffrey Snover, Bruce Payette, James Truher are the founding members of
Powershell.

With Powershell you can do everything and manage windows. We can use .NET, COM (Component
Object Model), ADSI (active directory) and WMI (Windows Management Infrastructure) objects in
Powershell. Powershell v1.0, released in late 2006,

Powershell is free tool; Powershell 2 is build it and is available with windows 2008 onwards

For windows shell, Powershell is the utility.

Run “Powershell” with cmd will get you to Powershell prompt.

C:\powershell

Powershell c:\>

For Sql server Powershell utility run “SQLPS” command. This will be free with sql server 2008 onwards

C:\>sqlps

Ps sqlserver:\>

You can use sqlps to do the following:

  •     Interactively run Powershell commands.
  •     Run Powershell script files.
  •     Run SQL Server cmdlets.
  •     Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects

http://msdn.microsoft.com/de-ch/library/cc280450(v=SQL.100).aspx



4|Page
                                                                           Powershell a day –April 2011




                         Day 2 Introduction to Windows Powershell.


Windows Powershell is a very powerful shell script to interact with windows.

Using windows Powershell you can access everything on windows system.

Some of the features we can achieve using Powershell:



   1. Active directory

   2. Exchange

   3. SQL Server Provider and Database Engine

   4. Registry

   5. Failure clustering

   6. Remote Desktop work

   7. Service Manager

   8. Virtual Machine

   9. IIS

   10. Windows administration

   11. Windows Server Backup


http://technet.microsoft.com/en-us/library/ee829690.aspx

http://en.wikipedia.org/wiki/Windows_PowerShell

There are 4 categories of commands in Powershell:

Cmdlets :      These are built-in commands in the shell, written in a .NET language like C# or
Visual Basic. Users can extend the set of cmdlets by writing and loading Powershell snap-ins.
Functions:     Functions are commands written in the Powershell language that are defined
dynamically.
5|Page
                                                                              Powershell a day –April 2011


Scripts:      ps1 extension containing a collection of Powershell commands.
Applications: executables.


Cmdlets are the commands for Powershell, generally Powershell is build in such a way that it uses similar
way of writing script like UNIX shell.

Command completion (tab) when you give half the command and tab it will automatically try to complete
the command (like – intelligence). And next (tab) will give the next similar command

E.g.

Get-c(tab) will gives you “Get-ChildItem” and another (tab) will gives you “Get-Command”

There are some common commands which will run for windows scripting /Powershell

       1. Get-Command :To get all the list of commands.

       2. Get-Help: to get the help of the command you can use get-help

E.g. If you want to get information about particular command eg. Get-command

Ps:\> Get-help get-command

          Will give you help about get-command

Comparison of cmdlets with similar commands(From Wikipedia*):-

                                       cmd.exe /           Bash
  Windows           Windows
                                    COMMAND.COM         (Unix, BSD,
 Powershell        Powershell                                                       Description
                                   (MS-DOS, Windows,    Linux, Mac
  (Cmdlet)           (Alias)
                                       OS/2, etc.)       OS X etc.)

                                                                          List all files / directories in the
Get-ChildItem       gci, dir, ls          dir               ls
                                                                                 (current) directory

Get-Content        gc, type, cat         type               cat              Get the content of a file

   Get-
                       Gcm               help             which              List available commands
 Command

  Get-Help          help, man            help              man                  Help on commands

 Clear-Host         cls, clear            cls              clear              Clear the screen[Note 1]

                                                                      Copy one or several files / a whole
 Copy-Item         cpi, copy, cp         copy               cp
                                                                               directory tree


6|Page
                                                                                         Powershell a day –April 2011


                                                                                   Move a file / a directory to a new
 Move-Item      mi, move, mv                 move                     mv
                                                                                                location

                ri, del, erase,
Remove-Item                        del, erase, rmdir, rd           rm, rmdir           Delete a file / a directory
                rmdir, rd, rm

Rename-Item     rni, ren, mv            ren, rename                   mv               Rename a file / a directory

                                                                                           Display the current
Get-Location       gl, pwd                     cd                    pwd               directory/present working
                                                                                                directory.

                                                                                  Change the current directory to the
Pop-Location        Popd                     popd                    popd           directory most recently pushed
                                                                                            onto the stack

                                                                                  Push the current directory onto the
Push-Location      Pushd                    pushd                   pushd
                                                                                                stack

Set-Location     sl, cd, chdir             cd, chdir                   cd            Change the current directory

                                                                                  Pipe input to a file or variable, then
 Tee-Object          Tee                      n/a                     tee
                                                                                   pass the input along the pipeline

                                                                                     Print strings, variables etc. to
Write-Output     echo, write                 echo                    echo
                                                                                            standard output

                                  tlist,[Note 2] tasklist[Note
Get-Process        gps, ps                      3]                     ps         List all currently running processes


Stop-Process      spps, kill      kill,[Note 2] taskkill[Note 3]   kill[Note 4]         Stop a running process

Select-String        n/a                find, findstr                grep            Print lines matching a pattern

                                                                                  Set the value of a variable / create a
Set-Variable       sv, set                    set                     set
                                                                                                variable




7|Page
                                                                             Powershell a day –April 2011




                        Day 3 Programming in Powershell Part 1
Now you got some good basic information about Powershell, and some basic commands cmdlets. In this
blog we will discuss how to write some basic scripting.

    1. Arithmetic expression: all the arithmetic expression supports Powershell i.e. +, -, *, / and %

E.g. Ps c:\>2+3

5

Ps c:\> 8%3

2

    2. Assignment operators: - Assignment operators are =, +=, -=, *=, /=, %=

    PS C:\> $a=1

    PS C:\> $b=2

    PS C:\> $c=3

    PS C:\> $d=4

    PS C:\> $e=$a+$b

    PS C:\> echo $e

    3

    PS C:\> $e+=$a

    PS C:\> $e

    4

    PS C:\> $e -=$a

    PS C:\> $e

    3

    PS C:\> $e *=$a

8|Page
                                                                               Powershell a day –April 2011


     PS C:\> $e

     3

     PS C:\> $e /=$a

     PS C:\> $e

     3

     PS C:\> $e %=$a

     PS C:\> $e

     0

     3. I/O Redirection:- we can redirect the i/o to or from other file .txt, also append: (>, >>, 2>,
        2>&1) it does not support input redirection ( get-Content)

     PS C:\> dir > a.txt

     4. Comparison operator:

Operator                                               Description

-eq –ceq –ieq                                          Equals

-ne –cne –ine                                          Not equals

-gt –cgt –igt                                          Greater than

-ge –cge –ige                                          Greater than or equal

-lt –clt –ilt                                          Less than

-le –cle –ile                                          Less than or equals

-contains -ccontains                                   The collection on the left hand side contains the value specified on th

-icontains

-notcontains-cnotcontains                              The collection on the left hand side does not contain the value on the

-inotcontains



     5. Format:

9|Page
                                                                                Powershell a day –April 2011



Cmdlet                         Description                                                          Example

Format-Table                   Formats a set of properties into a table                             dir | format-table name,

Format-List                    Displays properties 1 per line in a list.                            dir | format-list *

Format-Wide                    Displays a single property in multiple columns                       dir | format-wide

Format-Custom                  Complex formatter                                                    dir | format-custom



    6. Output:


Cmdlet               Description                                                            Example

Out-File             Writes formatted text to a file                                        dir | out-file –encoding unicode f

Out-Host             Writes formatted text to the screen                                    dir | out-host -pag

Out-Null             Discards all output (equivalent to > $null)                            dir | out-null

Out-Printer          Sends formatted output to the printer.                                 cat report.ps | out-printer

Out-String           Formats input as strings and writes them to the output pipe            dir | out-string | where {$_. mat



    7. Pipeline:- The pipeline is used to compose the output of the command.

PS C:\> dir | select -first 5

PS C:\> dir | select -last 5

PS C:\> dir | sort name | select -last 5

PS C:\> dir -filter *.bat

#display all sub directory as well

PS C:\> dir -filter *.bat –recurse

#to write comments




10 | P a g e
                                                                           Powershell a day –April 2011




                         Day 4 Programming in Powershell Part 2
Now we got some basic information about shell programming operators and other stuff. In this blog we
will try to discuss more about programming in Powershell.

Here most of the things will be similar to “C” programming language – you might have observed so far
☺. The core Powershell language is based on the POSIX 1003.2 grammar for the Korn shell. We can
change code between Powershell and C#.

Cmdlets always have names of the form Verb-Noun e.g. Get-childitem

There are some commands aliases which intern’s runs the command like “dir” is alias for get-childitem

PS C:\> get-command clear



CommandType            Name                   Definition

-----------    ----              ----------

Alias          clear                 Clear-Host




PS C:\> get-command cls



CommandType            Name                   Definition

-----------    ----              ----------

Alias          cls               Clear-Host

In day 2 of this blog Comparison of cmdlets with similar commands(From Wikipedia*) shows some of
the cmdlets with alias here <link>

To get the complete detail of any command pipe that command with get-command <Command> |fl
(Format List)

PS C:\> Get-Command get-childitem |fl


11 | P a g e
                                                                             Powershell a day –April 2011


Back tick (shift + before 1 (tilt) )escape sequences for string

`n       Newline

`r      Carriage return

`t      Horizontal tab

`a      Alert

`b      Backspace

`'      Single quote

`"      Double quote

`0      Null

``      A single backtick

The escape sequence can only be good when you use string with double-quote “.



Data type:

When you create a variable it can be assigned to any data type.

PS C:\> $a="Hello-World”

PS C:\> $a

Hello-World

PS C:\> $a=123

PS C:\> $a

123

Now if we want to explicitly assign a data type to a variable.

To write a variable to string : assign the variable start with @” and end with “@ , And string within the
block will be printed as it is.

and if we want to type things in tabular format assign the string starts with @{ and end with }

Array: assigning an array can be done using comma ,



PS C:\> [int]$a=1

12 | P a g e
                          Powershell a day –April 2011


PS C:\> $a

1

PS C:\> $a="avc"

This will give an error


Type Alias

[int]

[long]

[string]

[char]

[bool]

[byte]

[double]

[decimal]

[float]

[single]

[regex]

[array]

[xml]

[scriptblock]

[switch]

[hashtable]

[psobject]

[type]




13 | P a g e
                                                                                                 Powershell a day –April 2011




                               Day 5 Programming in Powershell Part 3
Now as you know all information, in this blog we will look for some conditional and looping
programming in Powershell. If you are programmer then you must be knowing how /what is conditional
statement and looping statement like if …. Then… else and for/while loop. Powershell is also support
them.

As Powershell is the object oriented programming, everything is object here. So object has

. Operator: where left is expand and right is expression.

PS C:\> "Powershell".length

10

$ : for variable declaration

@ : to call function

:: Operator : is s static member accessor

: Operator: for global variable

PS C:\> $global:a=12



In earlier blogs we discussed about conditional operators e.g. –gt, -lt

conditional statements and looping:

This is somewhat similar as C programming.

if/elseif/else

if (<pipeline>) {<statementList>} elseif (<pipeline>) {<statementList>} else {<statementList>}

PS C:\> $a=10

PS C:\> if ($a -lt 5) {echo "value is less than 5" }

PS C:\> if ($a -lt 5) {echo "value is less than 5" } elseif

14 | P a g e
                                                   Powershell a day –April 2011


>> ($a -lt 9) {echo "Value is less than 9"} else

>> {echo "Value is greater than 9"}

Value is greater than 9

{} are mandatory

Loop:

While Loop:

while ( <pipeline> ) { <statementList> }

PS C:\> $a=10

PS C:\> while ( $a -lt 20)

>> {echo $a;

>> $a++}

>>

10

11

12

13

14

15

16

17

18

19

do/while loop

do { <statementList> } while ( <pipeline> )

PS C:\> $a=10

15 | P a g e
                                                                   Powershell a day –April 2011


PS C:\> do {echo $a

>> $a++} while ($a -lt 20)

>>

10

11

12

13

14

15

16

17

18

19

for loop:

for ( <pipeline> ; <pipeline> ; <pipeline> ) { <statementList> }

PS C:\> for ($i=0; $i -lt 10; $i++) {echo $i}

0

1

2

3

4

5

6

7

8

16 | P a g e
                                                                      Powershell a day –April 2011


9

foreach loop:

foreach ( <variable> in <pipeline> ) { <statementList> }

PS C:\> foreach ($i in 1..10) {echo $i}

1

2

3

4

5

6

7

8

9

10

Foreach can be used with any command as well like

PS C:\> foreach ($i in dir *.txt) {echo $i}

label,break, and continue :this are similar command for c language.

Lable : to give lable to some statement

Break : break the loop

Continue: continue the loop

Switch :

switch -options ( <pipeline> )

{

<pattern> { <statementList> }
<pattern> { <statementList> }
default { <statementList> }
}



17 | P a g e
                                                                      Powershell a day –April 2011


PS C:\> switch (2) { 1 { "One" } 2 { "two" } default {"default"} }

two

PS C:\> switch (1) { 1 { "One" } 2 { "two" } default {"default"} }

One

PS C:\> switch (10) { 1 { "One" } 2 { "two" } default {"default"} }

default




18 | P a g e
                                                                            Powershell a day –April 2011




                               Day 6 Functions and error handling
Simple function

PS C:\> Function first {"Powershell is good"}

PS C:\> first

Powershell is good

Passing parameter to function

PS C:\> function second ($a,$b){$a-$b}

PS C:\> second 43 23

20

No need to separate the parameter with commas,

Specify the data type for the parameter

PS C:\> function second ([int] $a,[int] $b){$a-$b}

PS C:\> second 43 32

11

Parameter type                   Description

Switches                 Switches are present or absent, such as Get-ChildItem –Recurse

Options                          Options take an argument value, such as Get-ChildItem -Filter *.cs

Arguments                These are positional and don’t really have a name associated with them


return statement

PS C:\> function myreturn {return " Powershell"}

PS C:\> myreturn
19 | P a g e
                                                                             Powershell a day –April 2011


Powershell

[math]::sqrt(27)

.the functions we create will be temporary as soon as we exit from Powershell it will be deleted, so if we
want to keep the function permanent then script them in the flat file .ps1 and execute later whenever
you want.

>>We can store the command in a variable and run that command from that variable (similar to Pointer
C)

PS C:\> $i= gcm dir

PS C:\> &$i

Gcm – get-command

>> we can also assign command to variable.

PS C:\> $d=dir

PS C:\> $d |select name

Will work same as dir | select name

There is a command call psdrive which will show all the drives on your system includes “registry” drives.

Error Handling:

Whenever you run any errorrouse command you will get and error.

You can save /append that error into a flat file.

        PS C:\> dir mynameisd 2>a.txt

        PS C:\> type a.txt

        Get-ChildItem : Cannot find path 'C:\mynameisd' because it does not exist.

        At line:1 char:4

        + dir <<<< mynameisd 2>a.txt

Or

Can save that error into variable

        PS C:\> dir mynameisd -ErrorVariable errs1
20 | P a g e
                                                                              Powershell a day –April 2011


        Get-ChildItem : Cannot find path 'C:\mynameisd' because it does not exist.

        At line:1 char:4

        + dir <<<< mynameisd -ErrorVariable errs1

ErrorVariable allies is -ev

It has following properties

        PS C:\> $errs1 |gm -type property |select name



        Name

        ----

        CategoryInfo

        ErrorDetails

        Exception

        FullyQualifiedErrorId

        InvocationInfo

        TargetObject

$Error[0]

If error occurred by default that error will stored in variable $error[0]



        PS C:\> dir nosuchfile

        Get-ChildItem : Cannot find path 'C:\nosuchfile' because it does not exist.

        At line:1 char:4

        + dir <<<< nosuchfile

        PS C:\> $error[0]

        Get-ChildItem : Cannot find path 'C:\nosuchfile' because it does not exist.

        At line:1 char:4
21 | P a g e
                                                                             Powershell a day –April 2011


          + dir <<<< nosuchfile

          PS C:\>

$? And $LASTEXITCODE :

          PS C:\> dir mynameisd

          Get-ChildItem : Cannot find path 'C:\mynameisd' because it does not exist.

          At line:1 char:4

          + dir <<<< mynameisd

          PS C:\> $?

          False

---------------------

          PS C:\> dir mynm

          Get-ChildItem : Cannot find path 'C:\mynm' because it does not exist.

          At line:1 char:4

          + dir <<<< mynm

          PS C:\> $LASTEXITCODE

          1

---------------------

“When your previous command is having error $? Will be False and $LASTEXITCODE will be non zero”



Trap:

In the script if you want to trap the error use trap

trap [<exceptionType >]
{
<statementList >
}

Following script will give you an error


22 | P a g e
                                                     Powershell a day –April 2011


          &{

          1

          2/$zero

          3

          }

          "Error"

And we can trap the error like this

          &{

          &{ Trap { "$_";continue}

          1


          2/$zero

          3

          }

          "Good"

          }

Throw: -keyword

If we want to throw custom error we can use throw.

To debug the script

Set-PSDebug [-Trace <Int32>] [-Step] [-Strict ]
Set-PSDebug –Off




23 | P a g e
                                                                              Powershell a day –April 2011




                                           Day 7 – Security
With great power comes great responsibility.—Stan Lee, The Amazing Spiderman
So far We have discussed simple general things which generally every scripting language supports, but
as I stated eelier Powershell is comparable with “UNIX Shell Scripting” – which is also used for “system
programming” i.e. Interacting with system hardware /application. And handle it. Which is not easy and
knowing or unknowingly making changes on the system parameter or system setting /information could
cause system to crash or impact system performance. So Powershell can do almost everything in
“Windows” including application which works on Operating system as well as hardware/ drivers and so
on.

Microsoft operating system was build for desktop users so mostly it was used for general users who do
not have any knowledge or very little knowledge of computers. But as Microsoft is moving to enterprise
domain where it has to deal with big and very big environment they have to stand in the rase of other
competitors like UNIX. So they have developed “Powershell”. Which in terns uses .Net framework
language but it is very powerful and can do everything on windows.

So as the phrase stated “Powershell” is a very powerful. So what all I will be discussing now on has to
be little advance and need an extra attention and cautious while doing.

Please do not directly run/execute any code provided in this blog…. WE are not responsible for any
harm on you system due to this scripting.

This is general recommendation from me that – NEVER Ever run the script if you don’t know the
impact of it.

As discussed in earlier /initial blog I have mansion that with Powershell you can do anything. Like

>>Working with processes –> what if you stopped all the processes --- you are fired.. you are gone ☺

>>Working with Services -> what if you stopped all the services – congrats same result ---cool isn’t it.

>> working with AD -> SAME

>> working with Registry -> SAME

So knowing advance things are COOL but there minor mistake and you may looooose your job…. Hence I
am starting this blog here so that you will be more careful while working on the above topics in next
couple of blogs.

More on this… you may ask what Microsoft has taken as the security prospect.



24 | P a g e
                                                                             Powershell a day –April 2011


POWERSHELL Provides four EXECUTION POLICIES as follows (the information is from get-help
about_signing)

The Powershell execution policies are:

  Restricted

    - Default execution policy.

    - Permits individual commands, but scripts cannot run.



  AllSigned

    - Scripts can run.

    - Requires a digital signature from a trusted publisher on all scripts

     and configuration files, including scripts that you write on the

     local computer.

    - Prompts you before running scripts from trusted publishers.

    - Risks running signed, but malicious, scripts.



  RemoteSigned

    - Scripts can run.

    - Requires a digital signature from a trusted publisher on scripts and

     configuration files that are downloaded from the Internet (including

     e-mail and instant messaging programs).

    - Does not require digital signatures on scripts run from the

     local computer.

    - Does not prompt you before running scripts from trusted publishers.

    - Risks running signed, but malicious, scripts.

  Unrestricted

25 | P a g e
                                                                              Powershell a day –April 2011


    - Unsigned scripts can run.

    - Scripts and configuration files that are downloaded from the Internet

     (including Microsoft Outlook, Outlook Express and Windows Messenger)

     run after warning you that the file originated from the Internet.

    - Risks running malicious scripts.

Will Discuss more on securities later blog.




26 | P a g e
                                                                                 Powershell a day –April 2011




                                                   Day 8 Event Log
Event log is a very important trace and diagnose the activity on the system. Using Event Viewer it keeps
all the logs for that system. Generally you can open the event viewer using command Run -> Eventvwr
or start->Control Panel->Administrative tools ->Event Viewer.

The standard event logs are

         Application log

         Security

         System

There are additional logs depending upon the application on your system.

If we want to work on event logs it would be very easy to manage using Powershell because from the
tool it takes quite some time to work.

PS C:\> get-help get-eventlog -detailed

SYNTAX

  Get-EventLog [-logName] <string> [-newest <int>] [<CommonParameters>]

  Get-EventLog [-list] [-asString] [<CommonParameters>]

To get the list of event logs

PS C:\> get-eventlog -list



          Max(K) Retain OverflowAction                       Entries Name

          ------ ------ --------------   ------- ----

            512      7 OverwriteOlder                   4    ACEEventLog

         ...4,240     14 OverwriteOlder            12,007    Application

            512      7 OverwriteOlder                   0    Internet Explorer


27 | P a g e
                                                                            Powershell a day –April 2011


         16,384    0 OverwriteAsNeeded          8        Microsoft Office Diagnostics

         16,384    0 OverwriteAsNeeded          306      Microsoft Office Sessions

        ...4,240   14 OverwriteOlder       43,882        Security

        ...4,240   14 OverwriteOlder       15,834        System

         15,360    0 OverwriteAsNeeded          352      Windows Powershell

Generally all the application related logs would be in application log same for security and system. The
retention would be of 14days, which can be changed from Action->Properties option.

New event log option “Windows Powershell” or “Powershell” which maintains information and keep the
Powershell events tracked. Powershell also provides cmdlets command “get-eventlog”, with this
command we can read the event viewers logs (all logs) including Powershell log. For more information
on this.

To read the eventlog,

PS C:\> get-eventlog "Application"

Which gives you huge list of information, and which is not easy to read and screen will you scroll on and
on… to get better information about the log you have to use the condition/ check to newest option.

PS C:\> get-eventlog "Application" -newest 10

PS C:\> get-eventlog "System" -newest 10

PS C:\> get-eventlog " Windows Powershell " -newest 10

Powershell eventlog will also log the events for exchange servers.

Using condition

PS C:\>get-eventlog “Application” | where

To write the custom event at eventlog

[Diagnostics.EventLog]::WriteEntry("Application","Test Message","Information")

From windows vista (Powershell 2.0) onwards you can use --similar to this

PS C:\> get-WinEvent

View-> Analytic and Debug log

You can also trace the eventlog.
28 | P a g e
                                                                            Powershell a day –April 2011


As with other cmdlets objects to get the list of properties and methods of get-eventlog

PS C:\> Get-Eventlog "Application" |Get-member

In Powershell you can attach two or more cmdlet commands with | pipe for e.g. If you want to use
command and export the output to csv

PS C:\> Get-Eventlog "Application" | Where {$_.EntryType -eq "Error"} | Where {($_.TimeWritten).Date -
eq (Get-Date).Date} | Select Source, Message | Export-csv AppEvnt.csv

And to open the AppEvnt.csv which will be saved in current directory.

PS C:\>.\ AppEvnt.csv




29 | P a g e
                                                                             Powershell a day –April 2011




                                    Day 9 Process and Service

Service:
For Powershell we can also work with services. You can see which all services running on your system
using services.msc, services can be start /pause or stop. Using Powershell we can easily work on services
which generally takes time to open up service also can work on multiple services at the same time.

To get the list of services on your system. Get-service is the command



PS C:\>get-service

Now as you know you can use deferent properties on it to see specific services like get the list of
services which are currently in running step, the command would be

PS C:\> get-service | where {$_.Status -eq "Running"}

Now what if we want to stop/start some server(s),

PS C:\> get-service "SqlBrowser" | Start-service

Or

PS C:\> get-service | where ($_.Name –eq "SqlBrowser" }| Start-service

This command will start “SqlBrowser” service

remember my mistake if we don’t specify the condition or our condition is not exact you may end of
start/stop on all the services- which is very dangerous

so Powershell has a cool command option as –whatif or –Confirm which provides information about
whatif the given command run, and confirm would you like to run this command (y/n..) respectively.

PS C:\> Start-service | where ($_.Name –eq "SqlBrowser" }–whatif

Start-service and Stop-service is cmdlet commands

To get more information about service related commands

        PS C:\> gcm *service |select-object name

        Name
30 | P a g e
                                                                                                 Powershell a day –April 2011


          ----

          Get-Service

          New-Service

          Restart-Service

          Resume-Service

          Set-Service

          Start-Service

          Stop-Service

          Suspend-Service

Process:
Every application which is running on the system run as a process like notepad- when you open a
notepad at background it runs notepad.exe –notepad process –which you can confirm with Task
Manager(taskmgr) –Ctrl+Alt+Del ->Task Manager…

Similar to Services, we can work with processes like get list of processes start/stop…

*BY MISTAKE IF YOU GO STOP PROCESS FOR ALL YOU SYSTEM WILL CRASH

So remember these all commands are good but dangerous as well. So use it with caution.

PS C:\> get-process

Processes can be stopped only so another command is

PS C:\>stop-process

Remember to use –whatif or –confirm option before using this commands

Exporting to different objects

There are diffent objects we can export our output by default the output will be to “host” screen we can
convert to

PS C:\> get-process | convertTo-html

get-service | ConvertTo-Html -Property Name,Status | foreach { if ($_ -like "*<td>Running</td>*") {$_ -replace "<tr>", "<tr
bgcolor=green>"} else {$_ -replace "<tr>", "<tr bgcolor=red>"}} > .\get-service.html




31 | P a g e
                                                                                             Powershell a day –April 2011

Now as a sql server DBA – we do grouping of the query with group by command similarly we can group the similar objects output
together using Group-object command

PS C:\> get-service | Group-object   status

This will group all the running services in one group and stopped services in another

As you know services can be dependent on other services like sql server agent service is depends on sql
server services.

PS C:\> get-service | select status,name,dependentservices | where {$_.status -eq "Running"}

So if we have started the sql services

PS C:\> get-service *mssql`$* |start-service

It will be good, now if we try to stop the same service.

PS C:\> get-service *mssql`$* |stop-service

You will get an error something like this

         Stop-Service : Cannot stop service 'SQL Server (SQLSERVER2008SE) (MSSQL$SQLSERV

         ER2008SE)' because it has dependent services. It can only be stopped if the Force flag is set.

         At line:1 char:36

         + get-service *mssql`$* |stop-service <<<<

Here to stop the service which is depends on other service we have to use force option

PS C:\> get-service *mssql`$* |stop-service -Force




                         Day 10 Working with Directory and Registry
As you know, Powershell works with command prompt, and you know it works very well with all the
Windows commands for cmd.exe. the commands may not be exact but with the help of “Alias” it works.

So command prompt structure is like a “TREE” structure, where directory may contain sub-directories
and files.




32 | P a g e
                                                                                    Powershell a day –April 2011


To get list of directories cmd.exe is having command called “dir” which is also exists in Powershell ” get-
childitem” means Powershell has command(cmdlet) as “get-childitem” with alias as –dir, ls and gci.
which we can see with following command.

        PS C:\> get-alias | Where {$_.Definition -eq "Get-ChildItem"}

        CommandType              Name                    Definition

        -----------    ----                 ----------

        Alias          gci                  Get-ChildItem

        Alias          ls                   Get-ChildItem

        Alias          dir                  Get-ChildItem

Now to see all the drives on the Powershell-Psdrives command. Which shows all the drives present on
the system.

        PS C:\> psdrive




        Name          Provider       Root                         CurrentLocation

        ----     --------     ----                       ---------------

        Alias     Alias

        C        FileSystem C:\

        cert      Certificate \

        D        FileSystem D:\

        Env       Environment

        Function Function

        HKCU          Registry       HKEY_CURRENT_USER

33 | P a g e
                                                                              Powershell a day –April 2011


         HKLM     Registry    HKEY_LOCAL_MACHINE

         Variable Variable

Now here you could see that I have drives “C:, D : “ on my system so when I open the “My Computer” at
my system I could see C: and D:.

What about the remaining stuff. Here as Powershell treat everything like a directory so here “Alias” is
also a directory where we can create our alias. In the same way certificate, Environmental variable ,
Functions , Variables are also Directory, this way it is easy and convenient to access the values of this…..
that’s a very powerful stuff about Powershell. Where information is very handy. We have already
discussed about all this drives in our earlier blog. Just want to add here one thing that we can create our
own directory and our own alias, variable and so on… if require.

One more thing - To see the hidden files/directories use “-Force” option on gcm(get-Command) –
cmdlet

Now If we want to go into the psdrives given above –

e.g. if you want to move to “alias” drive from C:

         PS C:\> cd Alias:

You are done

         PS Alias:\>

You can work on that drive.

Now if you want to create your own drive new-PSDrive

PS C:\>help newPSDrive

SYNTAX

     New-PSDrive [-name] <string> [-psProvider] <string> [-root] <string> [-desc

     ription <string>] [-scope <string>] [-credential <PSCredential>] [-whatIf]

     [-confirm] [<CommonParameters>]

Eg: new-psdrive -name "V" -PSProvider Filesystem -Root ( "c:\Myfolder\Powerhsell")

Registry:

34 | P a g e
                                                                  Powershell a day –April 2011

Now you understand the concept of drives and how it goes. As you have seen the
output of PSDrive also contains two folders


        HKCU       Registry   HKEY_CURRENT_USER

        HKLM       Registry   HKEY_LOCAL_MACHINE

Which is nothing but registry information. Isn't it so COOOOOl…. Yeah. It’s very
nice and great feature. Generally you know how difficult it is to work with
registry information and how time consuming. Now. If you want to work – recurring
or programmatically read the registry information – the solution is Powershell…

Ok. How to work on registry… simple treat registry tree structure as tree
structure of directory and sub-directory and go through it.

For e.g. Suppose if you want to read the some information about Microsoft sql
server on your registry

For that you have to go to regedit->HKLM\software\microsoft\microsoft sql server

Great now you can do same and programmatically and use it where ever you want in
Powershell as

    1. Get the path into a variable

        PS C:\ >$MyPath=get-item HKLM:\software\microsoft\"Microsoft sql server"

        Now you variable is having all the data from that folder. You can see what
        all member from that variable using “Get-Member” (gm)

        PS V:\> $MyPath |gm




               TypeName: Microsoft.Win32.RegistryKey




        Name                           MemberType   Definition

        ----                           ----------   ----------

        Close                          Method       System.Void Close()

        CreateObjRef                   Method       System.Runtime.Remoting.ObjRef
        Create...

35 | P a g e
                                                               Powershell a day –April 2011

        CreateSubKey             Method        Microsoft.Win32.RegistryKey
        CreateSub...

        DeleteSubKey             Method        System.Void DeleteSubKey(String
        subke...

        DeleteSubKeyTree         Method        System.Void DeleteSubKeyTree(String
        s...

        DeleteValue              Method        System.Void DeleteValue(String
        name),...

        Equals                   Method        System.Boolean Equals(Object obj)

        Flush                    Method        System.Void Flush()

        GetAccessControl         Method
        System.Security.AccessControl.Registr...

        GetHashCode              Method        System.Int32 GetHashCode()

        GetLifetimeService       Method        System.Object GetLifetimeService()

        GetSubKeyNames           Method        System.String[] GetSubKeyNames()

        GetType                  Method        System.Type GetType()

        GetValue                 Method        System.Object GetValue(String name),
        ...

        GetValueKind             Method        Microsoft.Win32.RegistryValueKind
        Get...

        GetValueNames            Method        System.String[] GetValueNames()

        get_Name                 Method        System.String get_Name()

        get_SubKeyCount          Method        System.Int32 get_SubKeyCount()

        get_ValueCount           Method        System.Int32 get_ValueCount()

        InitializeLifetimeService Method       System.Object
        InitializeLifetimeServi...

        OpenSubKey               Method        Microsoft.Win32.RegistryKey
        OpenSubKe...

        SetAccessControl         Method        System.Void
        SetAccessControl(Registry...


36 | P a g e
                                                                 Powershell a day –April 2011

        SetValue                    Method         System.Void SetValue(String name,
        Obj...

        ToString                    Method         System.String ToString()

        Property                    NoteProperty System.String[]
        Property=System.String[]

        PSChildName                 NoteProperty System.String PSChildName=Microsoft
        s...

        PSDrive                     NoteProperty
        System.Management.Automation.PSDriveI...

        PSIsContainer               NoteProperty System.Boolean PSIsContainer=True

        PSParentPath                NoteProperty System.String
        PSParentPath=Microsoft....

        PSPath                      NoteProperty System.String
        PSPath=Microsoft.PowerS...

        PSProvider                  NoteProperty
        System.Management.Automation.Provider...

        Name                        Property       System.String Name {get;}

        SubKeyCount                 Property       System.Int32 SubKeyCount {get;}

        ValueCount                  Property       System.Int32 ValueCount {get;}




    2. As you define the variable with the required path of the registry. Which
        has its properties. As above. Some fot the important properties are – name,
        pspath, valuecount and property which shows name of the path, pspath –
        complete info about the resistry (very imp), how many items are present on
        that path and contents respectively.

        C:\>$Mypath.pspath

        #gives path location

        Now to see the property by which we can get values of the path

        PS C:\>$MyPath.valuecount

        3

        PS C:\>$MyPath.Property
37 | P a g e
                                                                          Powershell a day –April 2011


        IsListenerActive

        SsrpActiveServer

        InstalledInstances

        Great now to access each value separately we can get that into another
        variable

        PS C:\>$data1= get-itemproperty $MyPath.PSPath

        Done now you can access value of each property.

        PS C:\>$data1.InstalledInstances

        SQL2008

In this way you can read the values from anywhere in the registry.

You can create your own key into registry with

New-Item command

*I stated in my “Security” blog -I would suggest for production system working on advance topics
use Powershell to just reading the contents do not try to create, modify or delete the things”
hence I am not focus more of changing stuff in this series.

Finally, if you want to get who is having access to your folder/files the command
is

PS C:\>Get-acl




                                   Day 11 COM and WMI

Powershell is a very powerful language, with Powershell we can achieve same result with the several
ways. Microsoft programming language uses several objects COM (Component
Object Model), WMI (Windows Management Instrumentation) and .Net for programming, using
Powershell we can create these object as well.

While development Powershell has several cmdlets but it is not easy to make this cmdlets for each and
everything, so Powershell can use an existing objects for COM, WMI and .Net objects. To make

38 | P a g e
                                                                             Powershell a day –April 2011


Powershell more strong. There is command called “New-object” by which we can create an object for
any time. And use that object, which has its own methods and property. And interns that objects will be
of windows objects –COM/ .Net. so the compatibility wise it will be efficient.

As I am not a Windows programmer so I may not be the right person to go detailed into it but would like
to give an introduction on it.

.Net : As I stated earlier blogs that Powershell is uses .Net Framework. To use the . Net objects we have
to first load its assembly as default it’s not loaded into Powershell. Once we load the assembly we can
create an object of and manipulate that object including GUI objects.

Winform:

Eg.

[void][reflection.assembly]::LoadWithPartialName(
"System.Windows.Forms")
$form = New-Object Windows.Forms.Form
$form.Text = "My First Form"
$button = New-Object Windows.Forms.Button
$button.text="Push Me!"
$button.add_click({$form.close()})
$form.controls.add($button)
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()


This example will create a simple form and display dialog box with ok button, once we press ok the
dialog box will close.

COM : objects are objects like applications e.g. Xls, word, internet explorer, so using COM object we can
create an excel file and manipulate it.

Syntax.

New-Object [-ComObject ] <String > -strict

For just to understand it we will familiar with the terms in excels

Excel is having an object which contains “Workbook”, workbook contains “Worksheets”- sheets, and
every sheet contains cells (combination of rows and columns), so everything will be on workbook

Following is the steps for COM objects

      1. Create a new comobject

          $xl=new-object –comobject excel.application

          ##this will create a new excel application com object

      2. Add a new workbook object for that excel com object


39 | P a g e
                                                                               Powershell a day –April 2011


          $wb=$xl.workbook.add()

    3. Now on that workbook make an worksheet-

          $ws=$wb.worksheet.Item(1)

          Now you are ready with excel worksheet. So now on you are good to work with cells on
          worksheet and edit that.

    4. Work on worksheet cells

          $ws.cells.item(1.1)=”Powershell”

          ….

          ….

    5. This all this are working background, so if you would like to see the xls file. Make it visible.

          $xl.visible=$true

          If you want to disable the xls file

          $xl.visible=$False

    6. Now For saving this xls workbook.

          $wb.saveas(".\Com_xls_ps1.xls")



    7. To quit the excel file.

          $xl.quit()

Btw if you want to see the properties and methods belong to the object $xl you can use |get-member
(gm) – cmdlet –which you can use for any object to view their members

$xl |gm

Like this you can work on any application

internet Explorer

Word

Shell

40 | P a g e
                                                                                Powershell a day –April 2011


Shell is a very powerful object creation for COM object. There are two ways to work with shell.

    1. Create a shell application object.

         $Shell =new-object –COM shell.application

Using this object we can work on the COM application objects, e.g. Window explorer, Control panel. Etc.

Eg. $Shell.explore(“C:\”)

See more members using $shell |gm

Shell also support WScript.shell as it’s a more easy to work on the objects as

Create an object for WSScript.shell

         $WShell=new-object –COM WScript.shell

         $Wshell.Run(“Calc”)

if ($wshell.AppActivate("Calculator"))
{
$wshell.SendKeys("100")
start-sleep 10
$wshell.SendKeys("%{F4}")
}

This script will use the wshell and run the calc.exe which will open the calculator now if you want to
work on calc using Powershell. Activate it and send the keys using sendkeys method. At the end if you
want to close – send Alt+F4 to calc to close.

Isn’t it cool?.

WMI: Another way to work on windows is using WMI. Here things are little different. We have get-
wmiobject a separate cmdlet to work on it.

WMI is windows management used only for C++, VbScript in that also it was very difficult to code. On
the other hand WMI is very simple and powerful in Powershell.WMI is useful to run the script for
network computer as well.

Get-WmiObject
[-Namespace <String >]
[-ComputerName <String []>]
[-Credential <PSCredential >]
[-List ]

To see the list of options in Get-WMIObjects

Get-WmiObject –list

Very long list right… okay you can filter it. Like to see only win32 related objects

41 | P a g e
                                                                                          Powershell a day –April 2011


Get-WmiObject -list | where {$_ -match "win32_*"}

Looks good now. Right okay here are some of the common and important win32 wmi options.




         #to get the domain information

         $d=get-wmiobject win32_computersystem -property domain |select domain

         #infomation about operating system

         PS C:\> get-wmiobject win32_OperatingSystem

         #list of hotfixes

         PS C:\> get-wmiobject win32_quickfixengineering

         #Bios information

         PS C:\> get-wmiobject win32_Bios

         #logical drives

         PS C:\> get-wmiobject win32_Logicaldisk

         There are some other win32 options like

         PS C:\> get-wmiobject win32_Process

         PS C:\> get-wmiobject win32_Service

More information about that wmi

         PS C:\> get-wmiobject win32_Service | FL * |more

         FL * means you want all the information about the columns.




As I said we can get the same result with several ways so here it is we can use the get-process or get-
wmiobject win32_Process.




                                                      Day 12 ADSI
Using Powershell you can work on Active directory as well. Active directory is introduced in windows
2000. It’s a system where you can use domain server and create an organization unit –group of people
on one unit e.g. HR department. And then the users who are part of organization unit. for an enterprise
business there would be number of users working so for them ADSI is important.

Please do not try to make implement these stuff if you don’t understand and if you are not authorized to
do it. Especially if you are not the windows admin and having not much knowledge of it please don’t use
42 | P a g e
                                                                          Powershell a day –April 2011


the information given in this blog. For practice purpose you can download the ADAM(Active Directory
Application Mode) free for download from Microsoft and work on standalone system

Even I have inherited the examples from book – Powershell in Action

$domain = [ADSI] `
>> "LDAP://localhost:123/dc=NA,dc=Power1,dc=com"

Now create the Organization Unit- HR
$newOU = $domain.Create("OrganizationalUnit", "ou=HR")

$newOU.SetInfo()

Setinfo() is very important until this your information will not set.

Next add the user. For that we need to create the object for ou

$ou = [ADSI] "LDAP://localhost:389/ou=HR,dc=NA,dc=fabrikam,dc=com"

Now create a new object for new user

$newUser = $ou.Create("user", "cn=Dogbert")

Assign property for the $newuser object
$newUser.put(“Title”,”Mice”)
$newUser.put(“Employee ID”,1)
$newUser.Put("description", "Micheal")
$newUser.SetInfo()

And finally update the information onto the server.


Now if we want to inport/create multiple new users we can create an function for that

        function New-Employee ($employees
        {
        >> foreach ($record in $employees)
        >> {
        >> $newUser = $ou.Create("user", "cn=$($record.Name)")
        >> $newUser.Put("title", $record.Title)
        >> $newUser.Put("employeeID", $record.employeeID)
        >> $newUser.Put("description", $record.Description)
        >> $newUser.SetInfo()
        >>}
        }

You can import the new users via csv as

        >>New-Employee (Import-Csv Newusers.csv)
or import one by one or multiple calling function New-Employee


Now to get the users from ou another function

function Get-Employee (
>> [string] $name='*',
43 | P a g e
                                                                          Powershell a day –April 2011

>>   [adsi] $ou =
>>   "LDAP://localhost:123/ou=HR,dc=NA,dc=Power1,dc=com"
>>   )
>>   {
>>   [void] $ou.psbase
>>   $ou.psbase.Children | where { $_.name -like $name}
>>   }


Now if you want to search something in your AD need to create an object for
.DirectoryServices.DirectorySearcher


$ADDomain = [ADSI]“LDAP://dc=Power1,dc=local”
$ADSearch = New-Object System.DirectoryServices.DirectorySearcher
$ADSearch.SearchRoot = $ADDomain
# Definition of the filter: only computer, search for names

 $ADSearch.Filter = „(objectCategory=computer)“
 $ADSearch.PropertiesToLoad.Add(“name”)
$results = $ADSearch.FindAll()
# For the result we use a trick to only display the names:

Foreach ($res in $results)
 { $ADComp = $res.Properties
 $ADcomp.Name
}



Get information about domain

Get-DomainInfo
Or
Get-DomainInfo .

Quest company has also developed its own AD related cmdlets .

PSCX extiention is another one who make Active directory work as a PSDrive.

                                             Day 13 Other

XML:
Powershell support XML, as XML is a language which works great for windows, and it’s like a platform
independent, you can create a WebPages using xml.

With Powershell we can use convertto_XML option to convert an output to xml format.

Remoting:
It require Powershell v2
Windows Remote Management (WinRM).

Enable_remoting

help about_remote_troubleshooting
44 | P a g e
                                                                                     Powershell a day –April 2011



EnterPSSession -computer –computername

EnterPSSession -computer -computername (get‐content names.txt)


Email:
Email can be done using one of the following:
   a. Outlook
   b. .Net
   c. SNTP

    a. Outlook
$Outlook = New-Object .-com Outlook.Application
$Inbox = $Outlook.Session.GetDefaultFolder(6)
$Inbox.items | foreach { write-host $_.Subject }

    b.      For .Net
           System.Net.Mail
           System.Net.Mail.MailMessage
           System.Net.Mail.SmtpClient
           System.Net.Mail.Attachment


$Email = New-Object System.Net.Mail.MailMessage( “Info@contoso.local”,
“administrator@contoso.local”)

    c. SMTPClient

Coloring:
You can define coloring at the output as well

PS C:\> write-host -Fore Red "Powershell" -Back Green

More properties/methods:

If you see the get-member of any cmdlets you may get some properties/methods but that may not be
complete to get the complete list of properties and methods use the Format List (fl) option with * .

|Fl*

Profile:

As you know if you declare any variable or script anything that will be valid (available ) until you are using the
session, once you close the Powershell session your variables will be lost, to keep the variable or object for longer
use, declare it at profile which loads at the time of Powershell start

45 | P a g e
                                                                          Powershell a day –April 2011



To get the profile location there is a variable called “PROFILE”


PS C:\> variable profile |fl *


Name       : PROFILE
Description :
Value     : C:\Documents and Settings\<user>\My Documents\WindowsPowerS
        hell\Microsoft.PowerShell_profile.ps1
Options : None
Attributes : {}

Generally profile will be at location

C:\Documents and Settings\<user>\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1

Will try to update it further.




                                        Day 14 Tools and more info

As Powershell is a global language and its open to explore. So many great companies/ experts have
developed several tools with proper standards. We can use it as an expansion to existing Powershell

Tools:
   • http://www.codeplex.com/PowerShellCX
    •    http://www.primaltools.com/downloads/communitytools/.
    •    http://PrimalScript.com
    •    http://PowerShellPlus.com
    •    http://PowerGUI.org
    •    http://quest.com/powerguipro
         quest also developed its own active directory snapins
         Add-PSSnapIn Quest.ActiveRoles.ADManagement

46 | P a g e
                                                                           Powershell a day –April 2011


    •   http://ShellHub.com.
    •   http://www.codeplex.com/PowerShellCX
    •   www.sdmsoftware.com,
    •   www.fullarmor.com

I will try to keep it updated.




              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
47 | P a g e
                                                                               Powershell a day –April 2011



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/Indera) including
         features like intellisence and ADSI plug-in.
    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,
        cmd.exe
        c:\>Powershell
are we are done…. We can work with Powershell Script.

To work with Sql server there are several ways.

    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.


48 | P a g e
                                                                              Powershell a day –April 2011


Sqlserver CmdLets

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

No snapins are allowed

      2. . Net Assembly.
C:\windows\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
Smo.server
Smo.schema
Smo.script
Smo.table
Smo.sqlcommand
Smo.Backup
Smo.restore

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

Eg.
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:\>


49 | P a g e
                                                                                      Powershell a day –April 2011


       3. Snap-in:
         Snap 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.


       5. SMO.WMI
         $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.

                                   Day 16 Introduction to Mini Shell
Mini Shell or SqlPS is the default shell utility provided by Microsoft to support the sql server. This shell is default
comes with Powershell i.e. It will be default from sql server 2008 onwards. And it is supported by Microsoft,
means if you have any issue on your mini shell you can call PSS and they will help you out. Due to all this. It has
limited task to do with sql server.

The SQL Server PowerShell (SQLPS) snap-ins are as follows:

   •     Microsoft.SqlServer.Management.PSProvider.dll

        Implements the SQL Server PowerShell provider and associated cmdlets such as Encode-SqlName and
        Decode-SqlName.

   •     Microsoft.SqlServer.Management.PSSnapin.dll

        Implements the Invoke-Sqlcmd cmdlet.

This two snapins will in. we cannot register any external snapins (from any custom or third party spanins
) allowed in sqlps. Because external spanins can be changes by anyone at anytime and due to which
system may be unstable as sqlps is fully supported by Microsoft so any Microsoft does not support any
extended feature added by any third-party snapins.

Sqlps is most Good for invoke-sqlcmd, which is nothing but running command prompt sqlcmd.
Different ways to open sqlps/mini shell/ sql server Powershell.
    1. SQLPS
            a. As we run Powershell by just

50 | P a g e
                                                                           Powershell a day –April 2011


               C:\>Powershell
               Or
               Start->run ->Powershell

               b. In the same way
               SQLPS can be run from command prompt
               C:\>SQLPS
                Or
               Start->run->sqlps

Like for windows Powershell the shell will be like
PS C:\>

PS : Powershell and the C: is current directory

For sqlps
PS SQLSERVER:\>
SQLSERVERVER : is the sql drive. As we discussed in our earlier blog post when we were discussing about
“Registry, Variable and Alias. That when we run PSDrive Cmdlets it will show all the drives exist on the
system. And Powershell takes some components as drive. In the same way- Powershell treat “Sqlserver”
also as a drive .

When we run Sql Powershell (mini shell/SQLPS). And run psdrive
Eg. PSDRive.




Means it’s like a drive means all the drive commands/cmdlets can be used on it.

51 | P a g e
                                                                                Powershell a day –April 2011


Let’s start with dir/get-childitem/ls




Ohhh, great, here you can cd to “Sql”/ policy/ data collection. Cool.

SQL : directory is having all the sql server related information, it will show you all the sql instance
installed on your computer.




So when you reach to the server, you will get all the objects we can go into it. Like “databases” will be
same as databases in our management studio and so on…

    2. Management studio:
       Using management studio you can go to sql Powershell, this is supported from sql version 2008
       onwards where sql is build in. just open management studio and go to any tree object of sql
       server like database/table/views…. And right click, select the “Start Powershell” that’s it.
       Powershell will move to the respected folder where we pointed out.
       Eg. If we go to “database” and right click and start Powershell will go to “PS SQLSERVER:\sql\L-
       108326-b\sqlserver2008se\databases>” directory.



52 | P a g e
                                                                             Powershell a day –April 2011


Means using Powershell we can work with sql server just like we use directory, nice. And very fast. as
management studio may take some resources. That way performance improvement.

Sql Powershell has introduced some more cmdlets in addition to the windows cmdlets
Sqlserver CmdLets

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

Invoke-sqlcmd : will invoke the sqlcmd for Powershell to run any query on the database.


Eg.
PS: SQLSERVER\…\databases:\>Invoke-sqlcmd –Query “select * from master.sys.databases”

Will show you list of all the databases on the

or
PS: SQLSERVER\…\databases\master:\>Invoke-sqlcmd –Query “select * from sys.databases”

Generally when you are at “databases” folder and say “dir” you will get only user databases.
To get system databases as well in you dir cmdlets use switch “-force”

PS: SQLSERVER\…\databases:\>dir –force

                                    Day 17 Advance Mini Shell
Mini shell is a good and can be used as a replacement of management studio, in fact you can get lot of
detailed information.
For example in day 16 we discussed that

PS: SQLSERVER\…\databases\master:\>Invoke-sqlcmd –Query “select * from sys.databases”
Be default in windows Powershell the output is format-table means some limited number of columns
will be displayed. But here default format is format-list, which gives complete detailed information, in
this case detailed information about databases.




53 | P a g e
                                             Powershell a day –April 2011




Sql Powershell introduce following cmdlets


54 | P a g e
                                                                                  Powershell a day –April 2011


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



               1. Invoke-sqlcmd: as stated earlier, this cmdlet is used for calling the sqlcmd commands.
               2. Invoke-policyEvalution: it uses sql server 2008 onwords policy bases management
                  system.
               3. Encode-sqlname: sql server variants are having its own names which is not easy to
                  understand so this cmdlet is used to encode the variant
               4. Decode-sqlname: it is opposite to encode-sqlname
               5. Convert0URNtoPath: SMO uses URN and we can understand paths for directory.

        Encode-SqlName "Table:Test" returns the string "Table%3ATest".

        Decode-SqlName "Table%3ATest" returns "Table:Test".

PATH:

SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2008R2\Tables\Person.Address

And this is the URN to the same object:

Server[@Name='MyComputer']\Database[@Name='AdventureWorks2008R2']\Table[@Name='Address' and
@Schema='Person']



Adding the SQL Server Snap-ins to Windows PowerShell :

Ok this looks interesting ,but as we discussed. When we run sqlps it interns registering sql servers default span
ins

Add‐PSSnapin SqlServerCmdletSnapin100
Add‐PSSnapin SqlServerProviderSnapin100

So in the same way if we want to use sqlps from our Powershell we can manually add
those spanins. As

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

This will be same as running sqlps.

Take an example.

Run the psdrive at windows ps: you will not see “SqlSErver” drive.



55 | P a g e
                                                                           Powershell a day –April 2011


Add those snapins and run the psdrive again…. Yahoo… we got “SQLSERVER” drive.
Similar to what we get when we run sqlps.


With this on windows Powershell we can add other custom/thirdparty snapins. Also add
the assembly.

*Remember only sql server snapins (mini shell) is supported by Microsoft.




                                        Day 18 PSSnapin
Powershell works with cmdlets, and cmdlets are commands from snapins. Means spanin contains group
of commands. so if you want to know what all spanins installed /registered.

We can create cmdlets and spanins to extend the functionality of the existing Powershell to make shell
more powerful. But you know there are already so much snapins are available just to understand them
and use them may be much enough for now. But always there is a requirement for custom on.

By default there are 5 spanins installed on Powershell Version 1

And added 2 more ie. 7 for Powershell V2.

To get the list of pssnapins. The Get-PSSnapin cmdlets

PS C:\> get-pssnapin

Name      : Microsoft.PowerShell.Core

PSVersion : 1.0

Description : This Windows PowerShell snap-in contains Windows PowerShell management cmdlets
used to manage components of Windows PowerShell.



Name      : Microsoft.PowerShell.Host

PSVersion : 1.0

Description : This Windows PowerShell snap-in contains cmdlets used by the Windows PowerShell host.



Name      : Microsoft.PowerShell.Management

PSVersion : 1.0


56 | P a g e
                                                                           Powershell a day –April 2011


Description : This Windows PowerShell snap-in contains management cmdlets used to manage Windows
components.

Name       : Microsoft.PowerShell.Security

PSVersion : 1.0

Description : This Windows PowerShell snap-in contains cmdlets to manage Windows PowerShell
security.

Name       : Microsoft.PowerShell.Utility

PSVersion : 1.0

Description : This Windows PowerShell snap-in contains utility Cmdlets used to manipulate data.



They are

    1.     Core

    2.     Host

    3.     Management

    4.     Security

    5.     Utility
    6.     Diagnostics (V2)

    7.     WSMAN.Management (v2)



To get the detailed cmdlets containing each snapin

PS C:\> gcm -type cmdlet | ? { $_.pssnapin -match "Core"}

PS C:\> gcm -type cmdlet | ? { $_.pssnapin -match "Host"}

And so on.

In previous blog we discussed sql related snapins and associated with their cmdlets
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

*sqlps doesn’t have any cmdlets to get/add/remove snapins

Following snapins are excluded from sqlps




57 | P a g e
                                                                              Powershell a day –April 2011


         Add-PSSnapin

         Export-Console

         Get-PSSnapin

         Remove-PSSnapin



So snapin can be included only for Windows Powershell. As mentions in day 17 blog. If we include sql
related snapin into windows Powershell we are good to run the cmdlets which is at sqlps.



                                       Day 19 Sql Server with .Net
Using .Net programming is we can connect to sql server, its similar with any windows programming
(vb/vc/.net). where we uses connection and connection string. So the script written using .net will work
for any windows programming.

.Net process would be very lengthy and for a single sql command we have to write series of commands.
but we can use the template to do the connection string and once you are good with sqlconnection
procedure you can pass parameter for commands.

.Net has following Classes used to programming sql server.

>>Connection :open connection

>>Command :Sql command

>> DataAdopter : for result

>>DataSet: Result container

All these four classes are integrated with each other and depends on one another. We should follow the
same sequence to work.

    1.     Connection: the class connection is used to connect to the sql server, here you have to specify
         which sql server you are trying to connect. So need to create an object of connection to open
         the connection.

                          $sqlconnection =new-object system.data.sqlclient.sqlconnection

          Now you got a connection object. Assign the connection string for this connection, pass the
values which sql server you want to connect to.

                       $sqlconnection.connectionstring="Server=<serverName\instanceName>;datab
               ase=master;Integrated Security=True"

                          Where

                                server: is server name, if you have instance use it with “\” –backslash.

58 | P a g e
                                                                               Powershell a day –April 2011


                                 Database: default database

                                 Security: here you want default integrated security.



You can check the member of this variable

PS C:\> $sqlconnection |gm

Here you can see connectionstring property

    2.     Command: This is the very important piece of object where you will specify what command
         you want to execute on the server. You can specify the path/file which you want to execute at
         the server.

                     $Sqlcmd=new-object system.data.sqlclient.sqlcommand

                     $sqlcmd=get-content c:\query1.txt

         Now you can integrate the command with connection, means you will tell command that this
         command will run on this connection/sql server

                     $sqlcmd.connection=$sqlconnection

    3.     DataAdapter: Now the command is assigned, next need to create an object to store the data
         of that command. Ie. DataApapter

                     $SqlDataAdapter=new-object system.data.sqlclient.SqlDataAdapter

         Associate that adapter with the command.

                     $sqldataAdapter.selectcommand=$sqlcmd

    4.     dataset : Finally get the output on database, ie fill the dataset with adopter

                     $dataset=new-object system.data.dataset

                     $sqlDataAdapter.fill ($dataset)

$dataset.table[0].select()

$

DONE….. we have to create four objects to make it work.



So we can write any query on C:\query1.txt file and run that command on the particular sql server. Ie.
Create an .ps1(powershell) file for connection to sql server and another for query.

Run the .ps1 to run any query.



59 | P a g e
                                                                            Powershell a day –April 2011


Also you can pass the parameter to the .ps1 file to manipulate the connection string and other
parameter



*to execute the same command using sqlps :PS SQLSERVER:\> Invoke-sqlcmd –Database Master –Query
C:\Query1.txt



                                   Day 20 Introduction to SMO

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL
Server

The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object
model.

The DMO library is a COM object model, whereas SMO is implemented as a .NET Framework assembly

SMO is compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2,

The SQL Server provider automatically loads the SQL Server Management Object (SMO) assemblies

SMO classes include two categories:
The Microsoft.SqlServer.Management.Smo namespace contains classes that represent the core SQL Server
Database Engine objects. These include instances, databases, tables, stored procedures, and views.
        instance classes :databases, tables, triggers, and stored procedures
        utility classes: Transfer, Backup, Restore and scripter

Support all advance feature from sql server :

        Table and Index Partitioning

        EndPoints

        Snapshot Isolation/Row Level Versioning

        XML Schema Namespace, XML Indexes and XML datatype

        Full-Text Search Enhancements

        Page Verify

        Snapshot Databases

        Service Broker


60 | P a g e
                                                                                     Powershell a day –April 2011


         Index Enhancements

SQL Server Management Objects (SMO) Supports programming in Microsoft Visual Basic .NET, Microsoft Visual
C# .NET, and Microsoft Visual C++.

SQL Server Management Objects (SMO) uses the Microsoft System.Data.SqlClient (we discussed about this in
earlier blog)




Using smo classes you can do the following:

   •     Connect to an instance of the SQL Server Database Engine.
   •     View and modify instance settings and configuration options.
   •     View and modify database objects.
   •     Perform DDL (data definition language) tasks on an instance of the SQL Server Database Engine.
   •     Script databases dependencies.
   •     Perform database maintenance tasks, such as backup and restore operations.
   •     Transfer database schemas and data.



The list of assembly will be located at “C:\WINDOWS\assembly\” Folder

All the classes in the Microsoft.SqlServer.Management.Smo namespace are in following four files:
         Microsoft.SqlServer.Smo.dll
         Microsoft.SqlServer.SmoExtended.dll
         Microsoft.SqlServer.SqlEnum.dll
         Microsoft.SqlServer.SmoEnum.dll

You can get the list of all the smo classes

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

If you want to know which all Assemblies are loaded

[appdomain]::currentdomain.getassemblies()

Let’s start the simple example to write a simple script for SMO

Load the assembly

         [System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null

Create the object you want to work with eg. Server,database,table, backup….

For eg. Create an object for sever
         $Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘server\instance’

Now you are good to go and explore all the server related information.
61 | P a g e
                                                                                   Powershell a day –April 2011



Our great help…. Get-member

PS C:\>$server1 |gm



                                     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 server properties and methods used to work on sql server properties. It’s 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/en-us/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
62 | P a g e
                                                                                  Powershell a day –April 2011


        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
        EnumServerAttributes
        EnumServerPermissions
        EnumStartupProcedures
        EnumWindowsDomainGroups
        EnumWindowsGroupInfo
        EnumWindowsUserInfo



Database Class:


63 | P a g e
                                                                                          Powershell a day –April 2011



http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.aspx
          As “Server” class handles server related task, “Database” Class is on database related and can
          maniputed 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

Now if you want to run any query on the database.
         $q1= $database.executequerywithresults(“Sp_help”)

          $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 cover in next blog.




                                           Day 22 SMO programming Part 3

64 | P a g e
                                                                            Powershell a day –April 2011


In earlier blog we have discussed about server and database classes, using table class
you can create a table with different options.

Table class also require support of other classes like “Datatype” class and “Column” class
to specify the data type and column name respectively.

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

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

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

just need to define the Datatype variable

As we discussed for creating table object need to have database object (it can be $server objects ,
reference)but server object is must.

    1.   Load the assembly
    2.   Create server object
    3.   Create database object or create reference of server.databases[“dbname”] variable
    4.   Create a table object
    5.   Create datatype variables to define those datatype for our table
    6.   Create columns object(s) with reference to table and assign the datatype variable
    7.   Finally call create() method for table object

         [System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null
         $Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”)
         'Servername\InstanceNm’

         #we already seen how to create database object in earlier blog here we will use
         $server1.databases[“db”] variable
         $sDatabase1=$server1.databases[“Db1”]

         #we are assigning db1 means we will be working on DB1 and creating table.
         $table1= new-object (“Microsoft.SqlServer.Management.Smo.table”)($sDatabase1,”NewTable”)

         #now create datatype variable
         $datatype_int = [Microsoft.SqlServer.Management.Smo.Datatype]::int
         $datatype_varchar = [Microsoft.SqlServer.Management.Smo.Datatype]::varchar(10)


65 | P a g e
                                                                               Powershell a day –April 2011


        #create column objects
        $column_id = new-object
        (“Microsoft.SqlServer.Management.Smo.column”)($table1,”ID”,$datatype_int)
        $column_FNm = new-object
        (“Microsoft.SqlServer.Management.Smo.column”)($table1,”First_Name”,$datatype_varchar)
        $column_LNm = new-object
        (“Microsoft.SqlServer.Management.Smo.column”)($table1,”Last_Name”,$datatype_varchar)

        #add columns
        $table1.columns.add($column_id )
        $table1.columns.add($column_FNm)
        $table1.columns.add($column_LNm )

        #finally create a table
        $table1.Create()

        #Verify that the table is created.
        $sDatabase1.tables |select name

If you see the above example to create an object is little bit of work. But to get the object values just
creating an “Server” object is enough.

Eg.
Create a server object and access all the databases or tables in the database As:

        #to get the list of all the databases at server” $Server1”
        PS C:\> $server1.databases |select name

        #to get the list of tables from above selected databases (“DB1”]
        PS C:\> $server1.databases[“DB1”].tables


Means only server object can be used to access all the properties/methods/object on that server. To
create other object we require to create an object for that class.

Another very important thing is ….. the status of the object and respective values will be of that point
when the object is created.

In above example the $server object is created and we can see that the tables of database –DB1. Now if
we see
        PS C:\> $server1.databases[“DB1”].tables[“NewTable”].rowcount
        0

And from some other ways if we insert records into this table even though it will shows rowcount 0 as
the server object was created.


66 | P a g e
                                                                              Powershell a day –April 2011


Executequerywithresults is very helpful to run any query on particular server.
         $q1= $database.executequerywithresults(“select * from newTable”)
         $q1.tables |ft



Finally, as we can create any object we can alter, drop the object with .alter() or drop() methods
respectively. – this applies to all the objects- database/table/columns…


                                        Day 23 SMO programming Part 4
AS we can work with Server, Database, Table, Column. You can get the similar information about
Views, Storedprocedures

$database1.Views

$database1.Storedprocedures

$database1.logfiles

$database1.rules

$database1.schemas

$database1.roles

$database1.triggers

$tables1.triggers

IF YOU OBSERVED ON THIS, CMDLETS ARE ALWAYS IN SINGLE WHERE AS PROPERTIES ARE ALWAYS WITH (S)

LIKE FOR PROPERTIES – DATABASES, TABLES, COLUMNS, VIEWS, STOREDPROCUDURES, TRIGGERS

So all the properties are having “s” with it. But cmdlets are always in singular
Get-member, get-command, etc…

So all the objects associated with that object can be having (s).

[System.IO.Directory]::createdirectory(“C:\sql_bkp”)



Backup & Restore:

So far we have discussed about the database objects , create and manage it.

Now here we will talk about the Backup and restore – a very important for high Availability.
67 | P a g e
                                                                       Powershell a day –April 2011



Backup: backup is of three types
Full Backup
Tlog Backup
Differential Backup

Backup is also is having objects, we have to create an object of type
(“Microsoft.SqlServer.Management.Smo.Backup”) Backup and Restore classes are in SMOExtended.dll.
for that we need to resister Extended assembly .

FULL Backup:
       PS C:\>
       [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
       PS C:\> $bck=New-Object "Microsoft.SqlServer.Management.Smo.backup"
       PS C:\> $bck.Action = 'Database'
       PS C:\> $bck.Database ="DB1"
       PS C:\> $bck.devices.adddevice("C:\db1.bak","File")
       PS C:\> $bck.sqlbackup($server1)

TLog Backup:
       PS C:\>
       [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
       PS C:\> $bck=New-Object "Microsoft.SqlServer.Management.Smo.backup"
       PS C:\> $bck.Action = 'Log'
       PS C:\> $bck.Database ="DB1"
       PS C:\> $bck.devices.adddevice("C:\db1.log","File")
       PS C:\> $bck.sqlbackup($server1)

Differential Backup:
        PS C:\>
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
        PS C:\> $bck=New-Object "Microsoft.SqlServer.Management.Smo.backup"
        PS C:\> $bck.incremental = 1
        PS C:\> $bck.Database ="DB1"
        PS C:\> $bck.devices.adddevice("C:\db1_1.diff","File")
        PS C:\> $bck.sqlbackup($server1)

Remember to follow all the steps sequentially.

Restore on existing database:-
        PS C:\>
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
        PS C:\> $restore1= New-Object ('Microsoft.SqlServer.Management.Smo.Restore')
        PS C:\> $restore1.Database = "db1"
        PS C:\> $restore1.Action = database
        PS C:\> $restore1.ReplaceDatabase = $true
68 | P a g e
                                                                           Powershell a day –April 2011


        PS C:\> $restore1.Devices.AddDevice("c:\vin\db1.bak", "File")
        PS C:\> $restore1.NoRecovery = $false
        PS C:\> $server1.KillAllProcesses("db1")
        PS C:\> $restore1.SqlRestore($server1)

Restore on new database:-
Restore on new database requires relocation of data and log file to a new location. Which requires a new
“Relocation” objects for data and log files.
Logical and physical file name and file path.


        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.
        SMO") | Out-Null
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.
        SmoExtended") | Out-Null
        $Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”) 'L-
        108326-B\SQLSERVER2008SE’
        $restore1= New-Object ('Microsoft.SqlServer.Management.Smo.Restore')
        $relocateData1 = New-
        Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $relocateLog1 = New-
        Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $restore1.Devices.AddDevice("c:\db1.bak","File")
        $header1=$restore1.ReadBackupHeader($server1)
        $restore1.Database = "db2"
        $relocateData1.LogicalFileName = $header1.Rows[0]["DatabaseName"]
        $relocateData1.PhysicalFileName = “c:\DB2_Data.mdf"
        $relocateLog1.LogicalFileName = $header1.Rows[0]["DatabaseName"] +
        "_Log"
        $relocateLog1.PhysicalFileName = “c:\DB2_Log.ldf"
        $restore1.RelocateFiles.Add($relocateData1)
        $restore1.RelocateFiles.Add($relocateLog1)
        $restore1.SqlRestore($server1)

Restore the Tlog:
Restoring the tlog require restore the fullbackup with NoRecovery


        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.
        SmoExtended")
        $restore1= New-Object ('Microsoft.SqlServer.Management.Smo.Restore')
        $restore1.Database = "db2"
        $restore1.Action = "Log"
        $restore1.ReplaceDatabase = $true
        $restore1.Devices.AddDevice("c:\db1.log", "File")
        $restore1.NoRecovery = $true
        $restore1.set_NoRecovery(0)
        $server1.KillAllProcesses("db2")
        $restore1.SqlRestore($server1)



                                 Day 24 Performance Monitor

69 | P a g e
                                                                           Powershell a day –April 2011




Sql Performance:
   performance counters sys.dm_os_performance_counters
   Top ten queries from sys.dm_exec_query_stats and sys.dm_exec_sql_text
   Processor: sys.dm_os_schedulers
  Memory : sys.dm_os_memory_clerks


Performance counters using WMIobject:
        !JÌ H<MM@? <M<
         B@MQHDJ=E@>M   Win32_PerfFormattedData_PerfDisk_PhysicalDisk
         B@MQHDJ=E@>M Win32_PerfFormattedData_PerfOS_Memory
         B@MQHDJ=E@>M Win32_PerfFormattedData_PerfOS_Processor
         B@MQHDJ=E@>M Win32_PerfFormattedData_PerfProc_Process
         B@MQHDJ=E@>M Win32_PerfFormattedData_Tcpip_NetworkInterface

        Or
        ROW data
               Win32_PerfRawData_PerfDisk_PhysicalDisk
               Win32_PerfRawData_PerfOS_Memory
               Win32_PerfRawData_PerfOS_Processor
               Win32_PerfRawData_PerfProc_Process
               Win32_PerfRawData_Tcpip_NetworkInterface


        As we see the performance monitor can be using WMI now we can also use .net to get
        performance monitor information

Using PerformanceCounter Object (.NET):

        get-wmiobject Win32_PerfFormattedData_PerfProc_Process

        $pc = new-object system.diagnostics.PerformanceCounter
        [System.Diagnostics.PerformanceCounterCategory]::GetCategories()

        $cat = new-object System.Diagnostics.PerformanceCounterCategory("Process")
        $cat.GetInstanceNames()
        $cat.GetCounters("powershell")
        $cat.GetCounters()

         $pc.nextvalue() : is as good as Formatted Data

        $$pc.nextSample(): is as good as Row Data

Version Powershell V2:

        Powershell v2 has introduced new cmdlet for performance counter

70 | P a g e
                                                                          Powershell a day –April 2011



        get-counter -ListSet *



        Get-counter Gets real-time performance counter data from local and remote computers.
        Import-counter Exports PerformanceCounterSampleSet objects as performance counter log
        (.blg, .csv, .tsv) files.
        Export-counter Imports performance counter log files and creates objects that represent
        each counter sample in the log.


        http://technet.microsoft.com/en-us/library/dd367892.aspx


Perfmon:
      Standard gui way of getting the performance counters is perfmon, you can script this as well.

Using Sqlserver:

Sql server provides sys.sysperfmon


Using Data Collector

Reference:-

http://msdn.microsoft.com/en-us/library/system.diagnostics.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/31/how-i-use-powershell-to-collect-
performance-counter-data.aspx

http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/0b76c3e3-8c3f-428a-
8c50-b602301a84e1/

http://mow001.blogspot.com/2005/12/getting-performancemonitor-info-from.html

Keep it refreshing

http://blogs.technet.com/b/heyscriptingguy/archive/2005/04/21/why-does-my-performance-
monitoring-script-keep-returning-the-same-incorrect-values.aspx


                                           Day 25 RMO
I have observed that nobody has written anything about RMO. And replications is one of my favorite
topic, so tried to write this blog.
71 | P a g e
                                                                                     Powershell a day –April 2011



Please DO NOT TRY THIS SCRIPT ON PRODUCTION, I AM NOT RESPONSIBLE OF ANY CURRUPTION.

I worked on transaction replication so here is the Powershell script to create the transaction replication
with PUSH subscriber.

In this scenario I am using publisher and subscriber on the same box so no need to create a connection
object for subscriber server.


For more information about replication please see my blogs under “replication” Category.

                                      Create Distribution Database

To configure publishing and distribution on a single server

    1.   Create a connection to the server by using the ServerConnection class.

         $servername="ServerName"

         $srv = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $servername

         $srv.connect()

    2.   Create an instance of the ReplicationServer class. Pass the ServerConnection from step 1.

         $distributor = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $srv

    3.   Create an instance of the DistributionDatabase class.

         $dist_db= New-Object "Microsoft.SqlServer.Replication.DistributionDatabase" "distribution",$srv

    4.   Set the Name property to the database name and set the ConnectionContext property to the
         ServerConnection from step 1.




    5.   Install the Distributor by calling the InstallDistributor method. Pass the DistributionDatabase object from
         step 3.

         $distributor.InstallDistributor($srv, distributionDb)

    6.   Create an instance of the DistributionPublisher class.

         "Microsoft.SqlServer.Replication.DistributionPublisher"



72 | P a g e
                                                                                 Powershell a day –April 2011


                 $publisher = New‐object "Microsoft.SqlServer.Replication.DistributionPublisher" ("L-
         108326-b\SQLSERVER2008SE", $srv)




    7.   Set the following properties of DistributionPublisher:
            •     Name - name of the Publisher.
            •     ConnectionContext - the ServerConnection from step 1.
            •     DistributionDatabase - the name of the database created in step 5.
            •     WorkingDirectory - the share used to access snapshot files.
            •     PublisherSecurity - the security mode used when connecting to the Publisher.
                WindowsAuthentication is recommended.



         publisher.DistributionDatabase = distributionDb.Name

publisher = New DistributionPublisher(publisherName, conn)
    publisher.DistributionDatabase = distributionDb.Name
    publisher.WorkingDirectory = "\\" + publisherName + "\repldata"
    publisher.PublisherSecurity.WindowsAuthentication = True
         publisher.Create()


    8.   Call the Create method.


                                          Configure Publisher



publicationDb = New ReplicationDatabase(publicationDbName, conn)

$publicationDb =new-object "Microsoft.SqlServer.Replication.ReplicationDatabase” (“Vinay”, $srv)

$publicationDb. .EnabledTransPublishing =1

PS C:\> $publicationDB.LogReaderAgentPublisherSecurity.WindowsAuthentication=1

PS C:\> $publicationDB.CreateLogReaderAgent()

PS C:\> $publication=new-object "Microsoft.SqlServer.Replication.TransPublication"

PS C:\> $publication.ConnectionContext=$srv

PS C:\> $publication.DatabaseName="Vinay"




73 | P a g e
                                                                              Powershell a day –April 2011


PS C:\> $publication.Type

Transactional

PS C:\> $publication.Status

Active

PS C:\> $publication.SnapshotGenerationAgentPublisherSecurity.WindowsAuthentication=1

PS C:\> $publication.IsExistingObject

PS C:\> $publication.Name="VinayP"

PS C:\> $publication.Create()



                                            Assign Articles

PS C:\> $article =new-object "Microsoft.SqlServer.Replication.TransArticle"

PS C:\> $article.ConnectionContext=$srv

PS C:\> $article.Name="i"

PS C:\> $article.DatabaseName="Vinay"

PS C:\> $article.SourceObjectName="i"

PS C:\> $article.PublicationName="VinayP"

PS C:\> $article.Type

LogBased

PS C:\> $article.SchemaOption

PrimaryObject

PS C:\> $article.IsExistingObject

False

PS C:\> $article.Create()



74 | P a g e
                                                                          Powershell a day –April 2011




#**********************************************************************

                                 #Configure PUSH Subscription

$subscription =new-object "Microsoft.SqlServer.Replication.TransSubscription"



PS C:\> $subscription =new-object "Microsoft.SqlServer.Replication.TransSubscription"

PS C:\> $subscription.ConnectionContext=$srv

PS C:\> $subscription.SubscriberName="Servername "

PS C:\> $subscription.PublicationName="VinayP"

PS C:\> $subscription.DatabaseName="Vinay"

PS C:\> $subscription.SubscriptionDBName="Vinay_sub"

PS C:\> $subscription.AgentSchedule.FrequencyType

Continuously

PS C:\> $subscription.AgentSchedule.FrequencyType="onDemand"

PS C:\> $subscription.AgentSchedule.FrequencyType

OnDemand

PS C:\> $subscription.Create()

PS C:\>




Reference:

http://sqlblogcasts.com/blogs/antxxxx/archive/2011/04/04/scripting-replication-with-powershell.aspx

RMO Namespace :
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.aspx
          http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.scriptoptions.aspx



75 | P a g e
                                                                              Powershell a day –April 2011



Task:
http://msdn.microsoft.com/en-us/library/ms146869.aspx



                                      Day 26 Generate script

Generate script for sql server objects is very simple, there are different ways we can generate the sql
server objects includes – database, table, trigger, stored procedure…

    1. using SqlPS –MiniShell:

We have already discussed about mini shell in our blog post Day 16 and Day 17, were we discussed
about how simple is to work with sqlps, and invoke-sqlcmd command just by going to that directory and
working on that object. Like databases/tables….

To start it with SQLPS

PS SQLSERVER C:\>
You can move to folders and go till the databases/tables

PS SQLSERVER\SQL\Servername\InstnaceName\Databses\Table\>

Here if you see dir get table “Table1”

You can select that table1 and get-item into a variable

PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a=get-item dbo.Table1
PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a.script()

Method Script() is good to go for scripting the object. This can be applicatable to any object of the
sqlserver shell

To Get the script output into a file just redirect to any file as

PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a.script() |out-file
c:\table_table1.sql
or
PS SQLSERVER:\SQL\Servername\InstnaceName\Databses\Table\> $a.script() >c:\table_table1.sql

You can use any redirect operator.

This will be the same script which you will get when you right click the object from SSMS and select
“Generate Script”.


76 | P a g e
                                                                               Powershell a day –April 2011


We can also generate the script for all the objects and their dependent objects. Using loops

Below script will give all the script of all tables from current database

PS SQLSERVER:\SQL\Servername\InstnaceName\Databases\Table\> foreach ($tbl in Get-
ChildItem) {$tb1.script()}

Similarly, to get the script into a flat file.
PS SQLSERVER:\SQL\Servername\InstnaceName\Databases\Table\> foreach ($tbl in Get-
ChildItem) {$tb1.script() >>c:\tb1.sql}

Or
PS SQLSERVER:\SQL\Servername\InstnaceName\Databases\Table\> dir | %{$_.Script() | out-file
c:\Tables.txt -append}

As I mansion several times, Means once you know the concept and cmdlets you can achieve the result
with several ways.

To get the properties , lots of lots of info.

Gm (get-member)

$tb1 |gm


     2. Using SMO

For scripting the object need to create an object of type

$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter')
($SMOserver)
$scriptr.Script(“Object(s)”)



A great blog by

Ed Wilson, Microsoft Scripting Guy

Here it shows to script all the objects also with dependent objects as well.

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-
database-objects.aspx

Here I am showing just a part of sciprt which generate the script of table objects (
we can add all the objects like sp, views... and on variable $objects += and make it
in loop.) also can pass several parameters. Please see the link for more information.



PS C:\> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

77 | P a g e
                                                                           Powershell a day –April 2011


PS C:\> $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server')
“SqlServer\InstanceName”
PS C:\> $db=$SMOserver.Databases["Db1"]
PS C:\> $object=$db.Tables
PS C:\> $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($server)
PS C:\> $scriptr.Options.Indexes=1
PS C:\> $scriptr.Options.WithDependencies=1
PS C:\> $scriptr.Options.FileName="c:\tables.sql"
PS C:\> $scriptr.Script($object)

Here you could see the option “WithDependencies” will generate script with all the dependencies on it.

    3. Script the Replication

As we discussed the replication in our RMO blog post at day 25. Here we will discuss to generate the
script of Replication. There is a great blog by Anthony Brown
http://sqlblogcasts.com/blogs/antxxxx/archive/2011/04/04/scripting-replication-with-powershell.aspx

Like “scripter” object in SMO, RMO scripting requires “Microsoft.SqlServer.Replication.ScriptOptions”
object.

Here is the detail information about ScriptOptions
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.scriptoptions.aspx

So for Generating script for existing replication. We can just create an object for the
ReplicationDatabases after connecting to the instance. And then create an object of publiser and
subsriber
Finally generate the script for them.

There is another good blog for
http://www.scarydba.com/2010/03/01/powershell-for-batch-operations/

new-object Microsoft.SqlServer.Management.Smo.scripter
new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions

http://get-powershell.com/post/2011/04/04/How-to-Package-and-Distribute-PowerShell-Cmdlets-
Functions-and-Scripts.aspx




I will try to keep this blog updated about Generate scripting.




                                          Day 27 Inventory
78 | P a g e
                                                                                           Powershell a day –April 2011



As discussed earlier about wmi, wmi is a good for accessing inventory data from remote system as:

To get the Operating related information
    1. win32_OperatingSystem :OS info
PS C:\> get-wmiobject win32_OperatingSystem –computername “servernm”

Or

PS C:\> get-wmiobject –Query “select name from win32_OperatingSystem” –computername

Where –Query is just like sql query where you can go with “Where” clause as well. And specifying –Computername from where you
want to get the information(remote server).

     2.   Win32_PingStatus : Ping

     3.   win32_quickfixengineering :Hot fix

     4.   win32_Bios : #Bios information

     5.   win32_Logicaldisk : #logical drives

     6.   win32_Service :servicese

     7.   win32_ComputerSystem

     8.   win32_PhysicalMemory : Memory

There are so many win32 options : List is here

Get-WmiObject -list | where {$_ -match "win32_*"}



This way we can get almost all the necessary information about system. That can be exported into
xls(csv) file usingExport-csv or export.txt

You can read the txt file using get-content cmdlet or read the sql server database using smo/.net/invoke-
sqlcmd (shell).

Read the xls file:

     1. using –com

          $xl = new-object -com Excel.Application

          $wbk ="C:\abc.xls"

          xl.Workbooks.open($wbk)

          $xl.Cells.Item(1,1).Value()

79 | P a g e
                                                                            Powershell a day –April 2011


        ....

        $xl.Workbooks.Close()



And can update the xls cell.

http://kentfinkle.com/PowershellAndExcel.aspx



Now to get the sql related information.

http://www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-of-your-
servers/

                                          Day 28 SQL Policy
     I highly recommand to go through the Policy –based management (BCM)’s official
                         excellent site to learn more about PBM
                            http://blogs.msdn.com/b/sqlpbm/

Introduction:-
Policy-Based Management (PBM).is introduced from sql server 2008 onwards. Policy means making a sql
standard to follow and restrict the user/developer/dba to follow it. That way the system will be more
systematic and stable and easy to manage. Using PBM we can maintain the system compliance as well.

More detail here: http://blogs.msdn.com/b/sqlpbm/archive/2008/04/20/policy-based-management-
overview.aspx

Expression

Below link Don Jones explains different terms for policy

Eg.. if we want to keep all our stored procedure name to be start with “usp_%” we can restrict it using
policy

PBM Terms:
Policy: compliance or restriction .
Condition : here our condition is stored procedure name should be like “usp[_]%”
Facet: Stored procedure name /multi task names. There are several facets available in sql server
Target Type :Stored procedures

Here is a good example detail how to create a policy using
GUI:http://blogs.msdn.com/b/sqlpbm/archive/2008/04/26/creating-a-policy-with-the-gui.aspx

80 | P a g e
                                                                                   Powershell a day –April 2011


Here is another one to enforce recovery model to FULL :
http://www.sqlcoffee.com/SQLServer2008_0002.htm

PBM using Powershell:

Now you might have got an idea about what is PBM and how to configure it using SSMS. As this blog
series is about Powershell a day. Let’s start how can we work PBM using Powershell. As discussed about
mini shell (Sqlps) in our previous blog Day 16 and Day 17. Sql Powershell also support SQLPolicy folder

When we open sqlps we will get the prompt
PS SQLSERVER:\>
In this prompt when we see dir, we get several sub folders which includes “SQLPolicy”

As you gone though the links above how to create the PBM using GUI. For Powershell also you may have
to go with the same steps


Declarative Management Framework (DMF)
Microsoft.SqlServer.Management.Dmf.PolicyStore
http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.dmf(SQL.100).aspx

http://blogs.msdn.com/b/sethus/archive/2008/06/16/sql-2008-powershell-script-for-creating-a-policy-
and-saving-to-file.aspx

    1. Create a connection object from sql server
         $Conn=New-Object System.Data.SqlClient.SqlConnection
         $Conn.ConnectionString="Server=”server”;Database=master;IntegratedSecurity=True"


    2. create sdk connection for PolicyStore
         $sConn=New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($Conn)


    3.   Create object for policystore - Microsoft.SqlServer.Management.Dmf.PolicyStore
         $Pstore=New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($sConn)


    4. Create a condition object with policystore
         $condition=New-Object Microsoft.SqlServer.Management.Dmf.Condition ($Pstore,‘SP2’)

$operator=New-Object Microsoft.SqlServer.Management.Dmf.ExpressionNodeOperator("LIKE", "@Name", "’usp%’")
$condition.ExpressionNode=$operator
$condition.Facet=’StoredProcedure’
$condition.create()

Now once condition is created. Associate that condition into obectset for all the sp .
$objectSet=New-Object Microsoft.SqlServer.Management.Dmf.ObjectSet($Pstore,‘SP_ ObjectSet’)
$objectSet.Facet="StoredProcedure"

$objectSet.Create() | Out-Null

#Create policy
$SP_policy=New-Object Microsoft.SqlServer.Management.Dmf.Policy ($Pstore,”SP_Policy”)

81 | P a g e
                                                                              Powershell a day –April 2011


$sp_policy.Condition=$condition.Name
$sp_policy.ObjectSet=$objectSet.Name
$policy.AutomatedPolicyEvaluationMode="Enforce"
$policy.Enabled=1
$policy.Create() | Out-Null



To evaluate the policy
Invoke-policyevaluate
http://msdn.microsoft.com/en-us/library/cc645987.aspx


*Reference : Microsoft.SQL.Server.2008.Administration.with.Windows.PowerShell–by MAK & Yan


                                           Day 29 Advance Security

As discussed in earlier blog about “Security” Day 7. Continue on that we will discuss here -what all
security features Powershell provides. How to use them.

Security is good but securing the things without using is of no use, don’t use the code that will be secure,
we write code to use it. as Powershell is like other shell we can execute the code so we need write
considering Security.

Powershell developer has consider the security very seriously and has made this product perfrect and
tried to make it as secure and powerful as possible. It’s being heavily used because of its security.

Powershell is considering against the following Virus/Warms:

>>The Danom virus

>>The MSH/Cibyz worm

There is no exception about security Powershell compare to any other programming language coding.

    1. Avoid input from user

    2. Avoid descriptive errors

    3. Avoid use of passwords on code.

    4. Avoid provide full access/permission for executer

    5. Authenticate the user before executing code.

    6. Powershell code is default save as text file –secure.

    7. Consider “Code Injection” while writing code.

    8. Avoid running script from remote location
82 | P a g e
                                                                              Powershell a day –April 2011


    9. use proper “Execution Policy” by Powershell

    10. Sign the script and validate it.

    11. Encrypt the code.

    12. Avoid Invoke-Expression

    13. Try to make short code.

          For safe side Powershell scripts never be executable, by default it opens with notepad.

Avoid Invoke-expression:

as invoke-expression execute the cmd with “;” concatenating command as well.

Eg.PS :\> $cmd=1+1

PS :\>invoke-expression $cmd

What if

PS :\>invoke-expression $cmd ; “Vinay”

2

Vinay

What if

PS :\>invoke-expression $cmd ; del    –force c:\"


Bhoom…. That’s why avoid use of invoke-expression this can be used in Code Injection.

PATH & TEXTPATH:

Make a note of environmental variable PATH & TEXTPATH, as this paths are used to
executables.

Chose right execution policy: we already discussed about execution policy on day 7. Make
sure to choose right execution policy.

Creating a SecureString object

For dealing with the sensitive data like credit card info…. Create a secure object which is
encrypt the data.

Get-Credential cmdlet or the [System.Management.Automation.PSCredential] type
PS (1) > read-host -AsSecureString -prompt "Password"
Password: ***

83 | P a g e
                                                                                        Powershell a day –April 2011

System.Security.SecureString

$ss = new-object System.Security.SecureString

PS (12) > $ss.MakeReadOnly()

PS (13) > $ss.IsReadOnly()

This way we can make secure string as readonly where only read is permitted.




$secureString = ConvertTo-SecureString "Secure" `
-AsPlainText –Force


There are two cmdlets ConvertTo-SecureString and ConvertFrom-SecureString. they uses
the Windows Data Protection API(DPAPI)

Certificates:

Code should be used by user to whom we trust that’s come with “Certificates”. To create a certificate
we require makecert.exe generally it will be there on you system by default or build in on SDK
you can download

http://msdn.microsoft.com/en-us/windowsserver/bb980924.aspx

makecert.exe

get-command makecert.exe | fl
Set-AuthenticodeSignature script.ps1 $certificate

To Export the certificate use

Certmgr.exe

credentials:
we can also use the AD credential for authentication using Get-Credential

Get-Credential

This will ask the authentication for the script

PS:\> $credential =Get-Credential

When we run the above script it will ask for the authentication credential.

Get-Acl cmdlet: access control lists (ACL) list the users who can access the scripts.




84 | P a g e
                                                                              Powershell a day –April 2011


                                          Day 30 Best Practice

Having good Security is first best practice for any language no exception with Powershell. Having that we
should also we try to follow following best practices.

*Best practice is not always best. It may vary depends on our requirement. And good for one may not be
good for others.

    1. Follow standard coding format

    2. Document everything

    3. Test everything completely before implementing on production environment.

    4. Maintain versioning of the code –VSS

    5. Use Write-Debug or Write-Verbose

    6. Use Write-host and write-output

    7. Do not run the script if you don’t know the logic/result.

    8. Maintain security standard try to use allsigned security policy if possible.

    9. Consider Day 29 – securing your data. Like SecureString, certificates, etc.

    10. Cleanup stuff after you have done. Means drop the object(s) once you are done with it.

    11. Plan first before start, then design finally implement keep the system optimize.

    12. Avoid Shortcuts :try to use fully qualified pathnames.

    13. Try to use cmdlets for most.

    14.

Below is the link by scripting guy blog where he describe the parameter alias which are not
documented (only cmdlet alias are )

http://blogs.technet.com/b/heyscriptingguy/archive/2011/01/15/weekend-scripter-discovering-
powershell-cmdlet-parameter-aliases.aspx

Great book by ScriptingGuy(Ed Wilson) “Powershell Best Practice” and webcast about Best Practice:

http://msmvps.com/blogs/richardsiddaway/archive/2011/01/11/powershell-best-practices.aspx




85 | P a g e
                                                                     Powershell a day –April 2011




Other A Month A Day Blogs:-

Paul Randal:
http://www.sqlskills.com/BLOGS/PAUL/post/T-SQL-Tuesday-11-Misconceptions-about-
EVERYTHING!!.aspx

Month of Monitoring – Adam Mechanics
http://sqlblog.com/blogs/adam_machanic/archive/tags/month+of+monitoring/default.aspx

Glenn Berry:
DMV a Day:
http://sqlserverperformance.wordpress.com/2010/04/29/a-dmv-a-day-%e2%80%93-day-30/
Hardware:
http://www.sqlservercentral.com/blogs/glennberry/default.aspx

xevet a day: Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-
extended-events.aspx

wait type a day: Pinal Dave.
http://blog.sqlauthority.com/2011/02/28/sql-server-summary-of-month-wait-type-day-28-of-28/




References:

Books:
   1. Powershell in Action - Bruce Payette

    2. Windows Powershell & Powershell Best Practice – Ed Wilson

    3. Windows Powershell & Administrative Task using Windows Powershell - Frank Koch

    4. Microsoft SQL Server 2008 Administration with Windows Powershell - MAK &Yan

    5. Windows Powershell Cookbook -Lee Holmes
86 | P a g e
                                                   Powershell a day –April 2011


    6. Mastering Powershell - Dr. Tobias Weltner

MSDN/TechNet and Webcast.

Special thanks to GOOGLE….




87 | P a g e

								
To top