SQL Server 2008 has PowerShell and I am going to speak about the way they work together. However, some basics on PowerShell are needed to get the big picture. So let’s start with an introduction to the PowerShell technology.

During the Microsoft Techdays in Basel, Marc van Orsouw presented PowerShell. I would like to present you an overview of PowerShell based on his presentation while adding some microsoft msdn website definition and my commentar.

What is PowerShell?

Windows PowerShell is an extensible command-line shell and associated scripting language from Microsoft. It is not case sensitive.

In PowerShell, administrative tasks are generally performed by cmdlets (pronounced command-lets), specialized .NET classes implementing a particular operation.

Sets of cmdlets may be combined together in scripts, executables (which are standalone applications), or by instantiating regular .NET classes (or WMI/COM Objects).

This works by accessing data in different data stores, like the filesystem or registry, which are made available to the PowerShell runtime via Windows PowerShell providers.

In this article, I will use PowerShell 2.0 ( included in Windows 7 and Windows 2008 R2).

powershell 0

The first step: your rights

Before running any script, you must change your policies while default is restricted.
To see your current policy : Get-ExecutionPolicy

There are four policies:

  • Restricted: No scripts can be run. Windows PowerShell can be used only in interactive mode.
  • AllSigned: Only scripts signed by a trusted publisher can be run.
  • RemoteSigned: Downloaded scripts must be signed by a trusted publisher before they can be run.
  • Unrestricted: No restrictions: all Windows PowerShell scripts can be run.

To use scripts, you must set the policy to RemoteSigned:
 

Set-ExecutionPolicy RemoteSigned

Run PowerShell with administrator rights – right click on “Run as administrator” – to set it.

Frequently used commands

The four most important commands :

  • Get-Help: Windows PowerShell includes two basic types of help – command help and conceptual help.

Example of a command help: Get-Help about_* –> get the list of about functions

Example of conceptual help: Get-Help Get-Command –> Get the information of the Get-Command

  • Get-Command: get a list of all Windows PowerShell cmdlets

Get-Command has an alias: gcm (but best practice is not to use it in script)

  • Get-Member: get the properties and methods of objects

Example to see member of Get-Command : Get-Command | Get-Member

You can group the member by categories like Verb or Group:

A popular example to see the possibilities of a string:

  • Get-PSDrive: see the Windows PowerShell drives in the current session:

Others command frequently used :

  • Get-Date: see the current time and date:

  • Get-process: see the processes that are running on the local computer or a remote computer:

Another example for get-process to see the version for a process:

How the operators work:

The first step is to see the existing operator : Get-help operator

There are four types of operators:

  • Arithmetic: Arithmetic operators, calculating numeric values.
  • Assignment: Assignment operators, assigning one or more values to a variable.
  • Comparison: Comparison operators, that let you specify conditions for comparing values and finding values that match specified patterns.
  • Logical: The Windows PowerShell logical operators connect expressions and statements, allowing you to use a single expression to test for multiple conditions.

A nice operation…

Variables

A variable requires a $ (dollar sign) to be identified as a variable.
To display the value of a variable, you simplyneed to type the name of the variable and PowerShell kindly returns the content.
An example:

Navigating

Selecting one of the PowerShell drives is easy. It is almost like in MS-DOS!

You can use the same commands as you would do in MS-DOS, such as CD, DIR, MKDIR, etc.
Example: to get a certain property for a registry key, (or a regular folder for that matter) you
can use the Get-ItemProperty cmdlet:

Modules

A module is a package that contains Windows PowerShell commands such as cmdlets, providers, functions, variables, and aliases.

Get-Module: see the modules that have been imported or that can be imported during the current session:


You can find many modules on the codeplex site.

To import modules, use import-module. A very nice module is Poshcode.

And finally: PowerShell and SQL Server 2008

SQL Server installs Windows PowerShell and a set of SQL Server snap-ins that expose SQL Server functionality in Windows PowerShell. You can then code Windows PowerShell scripts that work with SQL Server objects. The scripts can be run in the Windows PowerShell environment, in SQL Server Management Studio, and as SQL Server Agent jobs:

What does SQL Server PowerShell feature (based on Michiel Wories Blog):

  • SQLPS: a minishell that gives you a complete pre-configured Powershell with all of SQL Server’s extensions preloaded.

  • SQL Server Agent integration: a new job subsystem for Powershell.
  • SQL Server Management Studio Integration: context menus on every applicable node in Object Explorer (with connection context reuse. Including SQL security!)
  • 4 new Providers: new providers for SQL Server relational engine, registered servers, Data Collection, and SQL Server Policy Management
  • SQLCMD integration: SQLCMD compatible script execution within Powershell
  • SQL Server Policy Management integration: allows for an evaluation of any Policy
  • Various other cmdlets: support the provider, such as conversion of a SMO Urn to a Powershell path, encoding and decoding of SQL identifiers.
  • SQL Server Powershell redist: allows you to install SQL Server Powershell with your application or on any machine you need to have it on.

Another more complete article about SQL Server Powershell and how it is working is comming soon….

😀