A SQL Cmdlet a Day 3 Variables

As with any scripting language PowerShell supports variables, but unlike many languages declaration and assignment is streamlined.  One of the coolest things about PowerShell variables is that data type assignment is automatic based upon the data type value.  Variable declaration is simply done by prefixing a “$” to the variable name.  The below statement creates a variable called “$var” and does an inline assignment of the string value “Hello World!!”.

$var = “Hello World!!”

By calling the variable within your command shell it will display the variable value.

As I mentioned data type assignment is based on the variable value so in this case we don’t need to formally assign a data type.  The data type can be retrieved by calling the GetType() method of the variable. $var.GetType()

To get a list of all members available we can pipe the $var variable to a Get-Member cmdlet:

$var | Get-Member

To get more information on “piping” you can look at the second blog in this series here.

Since the data type of this variable is a string we cannot perform operations on it that are not supported on this type, such as incrementing the variable by one:

$var ++

But we can easily change the variable type by assigning a numeric value.

$var = 1

$var ++

$var.GetType()

Again, these examples and concepts are pretty straight forward so let’s dial it up and take a look at working with arrays.  The only difference in creating an array is in the syntax of the value assignment.  The below cmdlet creates a variable called $Computers and assigns an array of values.  The type is displayed by calling the GetType method and then the values are displayed by using the zero based index.

$Computers = @(“Server1″, “Server2″, “Server3″)

$Computers

$Computers.GetType()

$Computers[0]

$Computers[1]

$Computers[2]

$Computers[2] = “Server4“

$Computers[2]

Pretty kewl right?  But what does this have to do with using PowerShell variables with SQL?  Glad you asked!!  We’ll start with a simple example of creating a variable that will be assigned the value of the localhost default instance,  The first thing I’ll point out is rather than a direct assignment I am using New-Object command and specifying the type:

$server = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)  -argumentlist “localhost”

With the assignment complete we can now call the properties or methods available:

$server.Databases

This effectively demonstrates how PowerShell can be used to easily create variables of SQL objects and return information about them, but we want to be able to programmatically administer SQL right?  Consider the common and simple scenario of changing the recovery model of a database.  Obviously this can be done using SSMS, but what about scripting this out in PowerShell.  SIMPLE!!  The previous cmdlet demonstrates creating a variable to hold the localhost default instance and query its database, but this same variable can also be used to change the recovery model of one or all of the associated databases.  The below cmdlet calls the TwitterSearch database and pipes the output to a Select query to show the name and RecoveryModel of the database.

$server.Databases[”TwitterSearch”] | Select name, RecoveryModel

Changing the RecoveryModel is as simple as calling the property from our variable and assigning the value and then calling the Alter method to apply the change:

$server.Databases[”TwitterSearch”].RecoveryModel = “Simple”

$server.Databases[”TwitterSearch”].Alter()

$server.Databases[”TwitterSearch”] | Select name, RecoveryModel

This hopefully provided some useful insight into using PowerShell variables to query SQL information and apply object settings.  All posts in this series have worked directly with PowerShell from SSMS so the next post will outline using the the Windows PowerShell Window to import the SQLPS module.