Passing Command-Line Arguments to PowerShell

You can use scripts to automate all of your SQL Server management processes because it allows you to know that the task is done consistently and with a minimum of effort. (You really don't want to use the SQL Server Management Studio, or SSMS, graphical interface to back up each database in your environment, do you?)

While you can put the specific server and database you want to manage into a script, you'd severely limit the usefulness of that script. By passing arguments on the command-line to your scripts, you can use the same script against many servers and databases, and know that it's going to work.

Related: Passing Command-Line Variables

In most of the scripts I use, I pass the server name (actually SQL Server instance, but since SQL Server Management Objects (SMO) is using the Server object, I use the term "server" to represent the instance).  By placing a "param" block as the first executable PowerShell sees in my script, it treats the variables I define there as command-line arguments, so in my scripts (after the comment-based help, which I described in a previous post), I'll define the server to connect with like this:

param {[string] $inst = $null}

So, if my script is called Get-DatabaseOptions.ps1, I'll run it against server WS12SQL01 using this command-line call:

PS C:Demos>./Get-DatabaseOptions.ps1 WS12SQL01

This will cause the $inst variable to contain the value 'WS12SQL01' at the start of script execution and will be used to connect to that server for its processing. This works great, but doesn't ensure that the caller will pass in the required parameter, and setting the initial value to NULL will cause that value to be used if the parameter is not supplied, causing errors to occur.

But, what if you supply a second argument? Well, PowerShell will return extra arguments by default in a collection called $args, which you can use in your script if you choose.

PS C:Demos> .Get-DatabaseOptions.ps1 WS12SQL01 AdventureWorks$inst = WS12SQL01$args = AdventureWorks

Since I didn't write my script to accept anything but the one argument, I choose to specify the CmdletBinding() directive, which tells PowerShell that only the defined arguments are allowed (among other things that are beyond the scope of this post).

CmdletBinding()]param ([string] $inst = $null)

And, if I try to include the AdventureWorks parameter, PowerShell throws the following error:

C:DemosGet-DatabaseOptions.ps1 : A positional parameter cannot be found that accepts argument 'AdventureWorks'.At line:1 char:1+ .Get-DatabaseOptions.ps1 WS12SQL01 AdventureWorks+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    + CategoryInfo          : InvalidArgument: (:) [Get-DatabaseOptions.ps1], ParameterBindingException    + FullyQualifiedErrorId : PositionalParameterNotFound,Get-DatabaseOptions.ps1

I can add the following line just prior to the definition of the $inst variable to require the input of the argument.

[Parameter(Mandatory=$true)]

Now, the script won't execute until I supply the argument. In fact, it returns the following message:

cmdlet Get-DatabaseOptions.ps1 at command pipeline position 1Supply values for the following parameters:inst: 

The script allows me to supply the parameter and then continues on with the supplied value.

It's important to note that, while I've been discussing the behavior of the param block with respect to command-line arguments, the behavior is the same for functions as well.

One of the helpful features of PowerShell cmdlets is the ability to add the -WhatIf argument to a cmdlet, and that causes PowerShell to evaluate the cmdlet and its arguments as though it would execute, but not actually execute the cmdlet. For example, issuing the cmdlet

Get-Service | Stop-Service

would produce results you'd find undesirable, but you could see what the results would be by adding -WhatIf, and PowerShell would then report each service and tell you that it's stopping that service.

What if: Performing operation "Stop-Service" on Target "Windows Store Service (WSService) (WSService)".What if: Performing operation "Stop-Service" on Target "Windows Update (wuauserv)".What if: Performing operation "Stop-Service" on Target "Windows Driver Foundation - User-mode Driver Framework (wudfsvc)".What if: Performing operation "Stop-Service" on Target "WWAN AutoConfig (WwanSvc)".

An additional feature of the CmdletBinding() directive, allows you to provide the same feature inside your script. To enable the capability, add the SupportsShouldProcess=$True inside the parentheses of CmdletBinding(). The $PSCmdlet variable is a built-in variable containing information about the call stack, and it has a property called ShouldProcess, which can be tested. If this property is set to $False, the -WhatIf argument was supplied, and you can avoid actually performing the script's defined functionality. Since it's clearer to code for $True conditions, our code will look like this.

[CmdletBinding(SupportsShouldProcess=$True)]param ([Parameter(Mandatory=$true)][string] $inst = $null) if ($PSCmdlet.ShouldProcess("$inst","Return Database Options")){write-output "Do stuff on server `$inst = $inst"}

If we add the -Whatif argument to our command, PowerShell returns this information.

PS C:Demos> .Get-DatabaseOptions.ps1 WS12SQL01 -WhatifWhat if: Performing operation "Return Database Options" on Target "WS12SQL01".

Without the -Whatif argument, we get the normal processing results.

PS C:Demos> .Get-DatabaseOptions.ps1 WS12SQL01Do stuff on server $inst = WS12SQL01

Let's take things one step further. Let's say that normally, you want to run your scripts against a set of servers, one at a time. We've talked about the pipeline, and you can make use of it to further automate your processes. PowerShell provides the ability to perform initialization, iterative, and wrap-up processes using the Begin {}, Process {}, and End {} blocks. The Begin {} block executes once, the Process {} block executes once for each value sent down the pipeline, and the End {} block executes once, when the pipeline is complete. You don't need to do anything at the beginning or end of the pipeline, but you can use the Process {} block to add the ability to take a set of servers from the pipeline and process our script against each one.

First, you need to add another option to our param block, the ValueFromPipeline argument. This is added simply to the Parameter definition clause.

param ([Parameter(Mandatory=$true,ValueFromPipeline=$true)][string] $inst = $null)

Now, you place the logic in your script inside the Process {} block, and you're done.

Process {if ($PSCmdlet.ShouldProcess("$inst","Return Database Options"))        {foreach ($svr in $inst){write-output "Do stuff on server `$inst = $inst"}}}

You can still run the script as you did before.

PS C:Demos> .Get-DatabaseOptions.ps1 WS12SQL01Do stuff on server $inst = WS12SQL01

But, if you create a collection of server names, you can pipe them to the script and it'll process them correctly as well.

PS C:Demos> $svr = 'WS12SQL01','WS12SQL02','WS12SQL03'PS C:Demos> $svr | .Get-DatabaseOptions.ps1Do stuff on server $inst = WS12SQL01Do stuff on server $inst = WS12SQL02Do stuff on server $inst = WS12SQL03

You can write easy scripts in PowerShell to get things done quickly, but as you get comfortable and want your scripts to be usable by your entire team, take advantage of the features of PowerShell command-line arguments to help those who use your scripts.

Related: Set Database Option Properties with PowerShell