Implementing –whatif in SQL Server SMO using PowerShell Advanced Functions


Performing Dangerous Operations in SQL Server

spider_man_artwork “With great power comes great responsibility…and the –whatif to help”
Ben Parker…and Laerte Junior

Fellows, we know that not exists small “messed“ in the DBA job. When it happens, always is in HUGE proportions.

Fortunately we have in the advanced functions a feature that we can implement .
The -whatif parameter which is nothing more than it will show to you what it will do without run the command that you are sending. It is  something like : “What am I about to do?” unless “Holy All Saints. Do I have a backup ?”

To implement this feature we need to set the SupportsShouldProcess argument in the [CmdletBinding()] . It is part of the Advanced functions confirmation methods and you can get a complete help typing help about_functions_cmdletbinding.

In it´s definition :

     “When the SupportsShouldProcess argument is set to true, it indicates that
      the function supports calls to the ShouldProcess method, which is used to
      prompt the user for feedback before the function makes a change to the
      system. When this argument is specified, the Confirm and WhatIf
      parameters are enabled for the function”

Let’s write a function to drop objects. It is a VERY dangerous operation, so it is better we implement a –whatif.

I created two functions to show . Get-MSSQLTable and GET-MSSQLProcedure. Why ?

Because the word is reusable functions,if fact this is part of my new Simple-Talk article and I hope you liked it. In this article I will show my technique to create solutions using PowerShell and reusable functions is the main technique.

I can say that is the same that we use in SQLPSX because I just learned with a member in the PowerShell Jedi Council , Master Chad Miller  .

So, to a better explanation we have 2 functions that returns 2 smo objects (table and Stored procedure) . Then we will pass by pipeline to the Drop-SQLObject.

See, it is not a Drop-SQLTable or Drop-SQLProcedure. It It is function that will perform a drop in any object that support this method.

[\Updated – Check if the object supports Drop method]

How can my function check if it is a Object that supports drop method ?

Just add

   1: ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})

in the ValidateScript parameter set :

   1:  

   2: param(

   3: [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]

   4: [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*" -and ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})] $SmoObject,

   5: [switch] $Force

   6: )

Again, thanks to Shay Levy to send me this tip Alegre

[Updated\]

My first step to this function , say Drop-SQLObject was to create it receiving by parameter a SMO object and dropping inside a process block, but I faced a problem with Foreach enumerations in pipeline , and unfortunatly to retrive the Database Objects (tables, stored procedures and so on)  in the SMO you will need to use enumerators:

   1: function Drop-SQLObject_1

   2: {

   3:     [CmdletBinding()] 

   4:     param(

   5:     [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]

   6:     [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*"})] $SmoObject

   7:     )

   8:     

   9:         process {

  10:             $SmoObject.drop()

  11:         }    

  12:  

  13: }

As both get-msssqltable and get-mssqlstoredprocedure use a foreach enumeration to tables, a common issue happens trying to drop the object.

Basically Enumerators can be used to read the data in the collection, but they cannot be used to modify the underlying collection.

Then an error showed to me :

An error occurred while enumerating through a collection: Collection was modified; enumeration operation may not execute..
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\sqlserver\SQLServer.psm1:1233 char:16
+         foreach <<<<  ($table in $tables)
    + CategoryInfo          : InvalidOperation: (Microsoft.SqlSe…dListEnumerator:SmoSortedListEnumerator) [], RuntimeException
    + FullyQualifiedErrorId : BadEnumeration

To solve this problem I used an array (Idea that I found in a forum that Jedi Boe Prox  – @proxb solved)  and begin/process/end block.

The problem was that when the execution back to the get-sqltable process, the enumerator was trying to do a modification in the collection.

Then was just load the enitire object in process block in a array $MyObject

   1: foreach ($drop in $SmoObject) {

   2:                         $MyObject += $drop

   3:                         Write-Host "Droping $($SmoObject.GetType().name) - $($SmoObject)"

   4:                     }

and effectively drop in the end block at the drop-sqlobject with a simple for in this array.

   1: $count = $MyObject.count 

   2:                     for ($i = 0; $i -lt $count; $i++) {

   3:                         $MyObject[$i].Drop()

   4:  

   5:                     }

after all the items be processed (end block) .

I don’t know it is the better solution to solve this limitation, but It works.

   1: function Drop-SQLObject

   2: {

   3:     [CmdletBinding()] 

   4:     param(

   5:     [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]

   6:     [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*"})] $SmoObject

   7:     )

   8:     

   9:         begin {

  10:                     $MyObject = @()

  11:             }    

  12:  

  13:         process {

  14:                     foreach ($drop in $SmoObject) {

  15:                         $MyObject += $drop

  16:                         Write-Host "Deleting $($MyObject[$i].name)"

  17:                     }

  18:                 }    

  19:         end {    

  20:                 $count = $MyObject.count 

  21:                 for ($i = 0; $i -lt $count; $i++) {

  22:                     $MyObject[$i].Drop()

  23:  

  24:                 }

  25:         }    

  26:  

  27: }

  28:  

Now I can use –whatif

   1:  

   2: function Drop-SQLObject

   3: {

   4:     [CmdletBinding(SupportsShouldProcess=$true)] 

   5:     param(

   6:     [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]

   7:     [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*"})] $SmoObject

   8:     

   9:     )

  10:     

  11:         begin {

  12:                     $MyObject = @()

  13:                 }    

  14:  

  15:         process {

  16:                 if ( $PSCmdlet.ShouldProcess("$($SmoObject.GetType().name) - $($SmoObject)") ) { 

  17:                         

  18:                         foreach ($drop in $SmoObject) {

  19:                             $MyObject += $drop

  20:                             Write-Host "Droping $($SmoObject.GetType().name) - $($SmoObject)"

  21:                         }

  22:                     }    

  23:                 }    

  24:         end {    

  25:                     $count = $MyObject.count 

  26:                     for ($i = 0; $i -lt $count; $i++) {

  27:                         $MyObject[$i].Drop()

  28:  

  29:                 

  30:                     }    

  31:         }    

  32:  

  33: }

You see that in the [CmdletBinding(SupportsShouldProcess=$true)] I am “enabling” whatif (it also enable the ConfirmImpact Argument – There is a example below in [UPDATED])and the if ( $PSCmdlet.ShouldProcess(“$($SmoObject.GetType().name) – $($SmoObject)”) ) is the condition to NOT whatif, or TO perform the operation.

What you put in the $pscmdlet.ShouldProcess quotas is what will be displayed in the screen.

In my case I am showing the type of the object with the method gettype (if it is a table, stored procedure..etc) and the object itself – schema and name)

Then if I use :

   1: Get-MSSQLTable -Server "YourServer" -Database "test" -TableName "Test*" | Drop-SQLObject -whatif 

Will display :

What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste1]”.
What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste2]”.
What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste3]”.
What if: Performing operation “Drop-SQLObject” on Target “Table – [dbo].[teste4]”.

And will not drop the table. The function is just showing to you what it will do

But if I change omitting whatif

   1: Get-MSSQLTable -Server "YourServer" -Database "test" -TableName "Test*" | Drop-SQLObject 

Droping Table – [dbo].[teste1]
Droping Table – [dbo].[teste2]
Droping Table – [dbo].[teste3]
Droping Table – [dbo].[teste4]

Dropped !!!!!

As you can see the drop-sqlobject is receiving by pipeline any SMO object, then you can use it any database object that has the drop method of course. Stored Procedures, Views, Functions, Database..etc

Try in your sample database to perform a whatif in all stored procedures that start with “sp_”

   1: Get-MSSQLStoredProcedure -Server "YourServer" -Database "test" -ProcedureName "sp_*" | Drop-SQLObject -whatif 

 

Complete code ——————————————————————————————-

   1: function Get-MSSQLTable {

   2:  

   3:     [CmdletBinding()]

   4:     param(

   5:     [Parameter(Position=0, Mandatory=$true)] [String]$Server,

   6:     [Parameter(Position=1, Mandatory=$true)] [String]$Database,

   7:     [Parameter(Position=2, Mandatory=$false)] [String]$TableName

   8:     )

   9:  

  10:     begin {

  11:  

  12:         [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 

  13:         $ServerName=New-Object "Microsoft.SqlServer.Management.Smo.Server" $server

  14:  

  15:     }    

  16:  

  17:     

  18:     process {

  19:         $ServerName.Databases | where {$_.name -eq "$Database" } | % {

  20:             foreach ($table in $_.tables) {

  21:             

  22:                 if ($tableName) {

  23:                     $tables = $table | where {$_.name -like "$tablename*"}

  24:                 } else {

  25:                     $tables = $table

  26:                 }

  27:                 

  28:                 Write-Output $tables

  29:                             

  30:             }

  31:             

  32:         }    

  33:     

  34:     }

  35:     

  36: }

  37:  

  38: function Get-MSSQLStoredProcedure {

  39:  

  40:     [CmdletBinding()]

  41:     param(

  42:     [Parameter(Position=0, Mandatory=$true)] [String]$Server,

  43:     [Parameter(Position=1, Mandatory=$true)] [String]$Database,

  44:     [Parameter(Position=2, Mandatory=$false)] [String]$ProcedureName

  45:     )

  46:  

  47:     begin {

  48:  

  49:         [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 

  50:         $ServerName=New-Object "Microsoft.SqlServer.Management.Smo.Server" $server

  51:  

  52:     }    

  53:  

  54:     

  55:     process {

  56:         $ServerName.Databases | where {$_.name -eq "$Database" } | % {

  57:             foreach ($Proc in $_.storedprocedures) {

  58:             

  59:                 if ($ProcedureName) {

  60:                     $procs = $Proc | where {$_.name -like "$ProcedureName*"}

  61:                 } else {

  62:                     $procs = $proc

  63:                 }

  64:                 

  65:                 Write-Output $procs

  66:                             

  67:             }

  68:             

  69:         }    

  70:     

  71:     }

  72:     

  73: }

  74:  

  75:  

  76:  

  77:  

  78: function Drop-SQLObject

  79: {

  80:     [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='High')] 

  81:     param(

  82:     [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]

  83:     [ValidateScript({$_.GetType().Namespace -like "Microsoft.SqlServer.Management.Smo*" -and ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})] $SmoObject,

  84:     [switch] $Force

  85:     )

  86:     

  87:         begin {

  88:                     $MyObject = @()

  89:                 }    

  90:  

  91:         process {

  92:                 if ( $force -or $PSCmdlet.ShouldProcess("$($SmoObject.GetType().name) - $($SmoObject)") ) { 

  93:                         

  94:                         foreach ($drop in $SmoObject) {

  95:                             $MyObject += $drop

  96:                             Write-Host "Droping $($SmoObject.GetType().name) - $($SmoObject)"

  97:                         }

  98:                     }    

  99:                 }    

 100:         end {    

 101:                     $count = $MyObject.count 

 102:                     for ($i = 0; $i -lt $count; $i++) {

 103:                         $MyObject[$i].Drop()

 104:  

 105:                 

 106:                     }    

 107:         }    

 108:  

 109: }

 110: #

 111: Get-MSSQLTable -Server "xx" -Database "teste" -TableName "Test*"

 112: #

 113: Get-MSSQLTable -Server "xx" -Database "teste" -TableName "Test*" | Drop-SQLObject -whatif 

 114: Get-MSSQLTable -Server "xx" -Database "teste" -TableName "Test*" | Drop-SQLObject -Force 

 115: #

 116: Get-MSSQLStoredProcedure -Server "xx" -Database "teste" -ProcedureName "test*" | Drop-SQLObject -whatif 

 117: Get-MSSQLStoredProcedure -Server "xx" -Database "teste" -ProcedureName "test*" | Drop-SQLObject 

 

Now I can say that I am feeling the Power of the PowerShell.

[Updated]

Another member in the PowerShell Jedi Council, Master Shay Levy, (@shaylevy)  had a  good idea. Why not add auto confirmation wich you can override using –force switch ?

Just add ConfirmImpact argument in the cmdletbinding line :

   1: [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='High')] 

and add $force in the  conditions :

   1: if ( $force -or $PSCmdlet.ShouldProcess("$($SmoObject.GetType().name) - $($SmoObject)") ) { 

   2:  

   3: and

   4:  

   5: if ($force -or (-not $whatif)  ) {

This way, to each drop will show to you a window to confirm the operation :

image

Haa Laerte, but I need to drop tons of objects,to each one a windows will show ?

Or you select the option Yes to All in the window displayed,

or Just pass –force  parameter in the cmdlet call

   1: Get-MSSQLTable -Server "7-pc" -Database "teste" -TableName "Test*" | Drop-SQLObject -Force 

BIG THANKS SHAY Alegre !!!!!!!!!

Watch de VIDEO !!!

 

imagesCAFOH990  

This example , and all the others that will be in my new S-T article, are basically one of my session abstract that I submitted to PASS SUMMIT 2012 and you can see my abstracts in here.

If, by some gift of the universe, I have the chance and honor to be chosen, I will show to you the entire article in practice. No slides, just pure PowerShell Code in our heads.

Resources :

Sean Kearney (@energizedtech)
Implementing the–WHATIF into an Advanced Function in Windows Powershell

Chad Miller – (@cmille19)
Sev17 – SQL Server, PowerShell and so on
Codeplex – SQLPSX (SQL Server PowerShell Extensions)

 

 

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, Virtual Pass BR. Bookmark the permalink.

2 Responses to Implementing –whatif in SQL Server SMO using PowerShell Advanced Functions

  1. concentrateddon says:

    So, technically, when you set ConfirmImpact to High, you’ll always get auto-confirmation. The “correct” way to bypass that is to run the command with -confirm:$false – but adding -force isn’t totally wrong. -Force is usually used to override a permissions problem, read-only, or something else; using it to suppress auto-confirm is a bit nonstandard, but it is comprehensible.

    Don’t forget that -confirm and -whatif are also passthrough parameters. That is, if your function declares SupportsShouldProcess=$True, and someone runs the function with -whatif or -confirm, those will be passed to any other cmdlets WITHIN YOUR FUNCTION that also support -confirm and -whatif. Since your change isn’t being made by a cmdlet, you can’t take advantage of that, so the If construct and using $psCmdlet.ShouldProcess() is indeed the right way to go.

    Just wrote a chapter this morning for the new “Toolmaking Month of Lunches” book on this very topic!

    It does seem as if it would make more sense just to go ahead and drop the object inside the If construct, rather than appending it to a collection and then later enumerating that collection to drop objects – but that might be something you have to do in order to make the underlying technology happy?

    And you really ought to use Write-Verbose, rather than Write-Host, for those status updates in your script. Just saying’ :).

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s