Performing Dangerous Operations in SQL Server
|“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 ?
in the ValidateScript parameter set :
Again, thanks to Shay Levy to send me this tip
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:
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
and effectively drop in the end block at the drop-sqlobject with a simple for in this array.
after all the items be processed (end block) .
I don’t know it is the better solution to solve this limitation, but It works.
Now I can use –whatif
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 :
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
Droping Table – [dbo].[teste1]
Droping Table – [dbo].[teste2]
Droping Table – [dbo].[teste3]
Droping Table – [dbo].[teste4]
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_”
Complete code ——————————————————————————————-
Now I can say that I am feeling the Power of the PowerShell.
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 :
and add $force in the conditions :
This way, to each drop will show to you a window to confirm the operation :
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
BIG THANKS SHAY !!!!!!!!!
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.