PowerShell for SQL Server - SQL Server 2008

PowerShell is a new command-line shell and scripting language. It offers more power and functionality than VBScript and the Windows command prompt. PowerShell will be available in Windows Server 2008 technologies/ management/ powershell/ download.mspx) for other versions of Windows. PowerShell is designed to make it easy for vendors and developers to create providers that add value to PowerShell users. SQL Server 2008 provides a PowerShell provider that enables users to easily access SQL Server instances and SMO objects, and manage database policies within the PowerShell environment.

First, we’ll provide a brief introduction to PowerShell in Windows, and then explain how to use the SQL Server PowerShell provider.

Introducing PowerShell

PowerShell was designed to make it easier for information technology administrators to manage the Windows environment. So why should we learn yet another scripting language and command line? PowerShell is the result of years of effort by the Windows team at Microsoft. They took a hard look at the current scripting experience, customer feedback, and the user experience with other, more robust scripting environments. With the knowledge obtained, they created a powerful scripting and command shell environment that finally competes with ones found in other operating systems.

The functionality of PowerShell revolves around what are called cmdlets (pronounced “commandlets”). You can think of cmdlets as executables that perform a specific task. PowerShell provides more than 120 cmdlets out of the box. If that is not enough, you can write your own cmdlets easily using .NET, or you can purchase third-party cmdlets.

One of the great features of PowerShell is consistency. PowerShell cmdlets always have the same format of verb-noun. Issuing the statement Get-Command would result in a list of all the available cmdlets.

Some PowerShell Cmdlets

Some PowerShell Cmdlets

Some PowerShell Cmdlets

Some PowerShell Cmdlets

Get-Command provides a basic definition for each cmdlet. Get-Help will return more information about a specific command. For example, issuing Get-Help Get-ChildItem gives these results:

Gets the items and child items in one or more specified locations.
Get-ChildItem [[-path] <string[]>] [[-filter] <string>] [-include <string[]>]
[-exclude <string[]>] [-name] [-recurse] [-force] [<CommonParameters>]
Get-ChildItem [-literalPath] <string[]> [[-filter] <string>]
[-include <string[]>] [-exclude <string[]>] [-name] [-recurse]
[-force] [<CommonParameters>]

The Get-Childitem cmdlet gets the items in one or more specified locations. If the item is a container, it gets the items inside the container, known as child items. You can use the Recurse parameter to get items in all child containers. A location can be a file system location, such as a directory, or a location exposed by another provider, such as a registry hive or a certificate store.

As you can see, some information is missing here, like the definition and accepted values of parameters. You can obtain more information, including examples, by using the –default or –full option when displaying help.

As an example, let’s create a script that deletes our backup files (any file that has a .BAK extension) if they are more than ten days old. The delete_backups.

#Define our variables to be used in our script
$TargetFolder = "C:Backups"
$RetainBackups = 10
$Today = Get-Date
$LastWrite = $Today.AddDays(-$RetainBackups)
Write-Host "Removing backups from " $TargetFolder
Write-Host "Which are older than " $LastWrite
$Files = Get-Childitem $TargetFolder -include *.* -Recurse | Where
{$_.LastWriteTime -le "$LastWrite"}
if ($Files.count -gt 0)
foreach ($File in $Files)
Write-Host "Deleting File $File"
Remove-Item $File | out-null
Write-Host "There are no files to delete." –foregroundcolor "Red"

PowerShell scripts have the .ps1 extension. To run this script in PowerShell, simply type the filename at the PowerShell command line—in this case, delete_backups.ps1. As you can see from the script, the concept of an object is heavily used, without the need to define a specific datatype for each variable.

If you start working with PowerShell, you may notice that familiar command-line statements like DIR, CD, MKDIR, and RMDIR work perfectly. These are not the actual executables that you would find in a regular Windows command prompt. Rather, in PowerShell they are simply aliases to similar cmdlets that perform the task. For example, the command DIR is really an alias for Get-ChildItem. You can create your own aliases as well.

At the time of this writing, PowerShell version 2.0 is in beta form and expected to be released in the near future. Version 2.0 will add even more capabilities:

  • Script debugging: Set breakpoints, and step into and out of scripts using the PowerShell console window itself, without the need for specialized development tool.
  • Background jobs: Configure commands to run as asynchronous background jobs.
  • Graphical PowerShell: See color-coding and line numbers, and use a multiple document interface.
  • Script cmdlets: Write cmdlets using PowerShell scripts. You won’t need to write them using a formal programming language like C#, Visual Basic .NET, or any other .NET language.

PowerShell is a powerful addition to the administrative capabilities of the Windows operating system. Not only does SQL Server 2008 provide out-of-the box integration, but other products like Microsoft Exchange already have support.

Using SQL Server PowerShell

SQL Server 2008 contains a PowerShell provider out of the box. To launch SQL Server PowerShell, you can run SQLPS from a command line. Alternatively, you can select Start PowerShell from the context menu of a server instance in SQL Server Mangement Studio.

Those who have used the Windows command line will immediately notice the familiar text-based user interface experience. Just like the command line, PowerShell has the requirement that all providers must support a drive-qualified path. if you want to perform work on your C drive on your local file system, you would access it by using the drive letter C:. And if you wanted to navigate through your SQL Server machine, you would use SQL: for the drive-qualified path.

If you launch SQL Server PowerShell from Management Studio, it automatically defaults the drive to the instance where you launched PowerShell.launching it from a default instance yields the drive path to be SQL:<server name>DEFAULT.

Issuing Get-PSDrive at the command line results in an enumeration of the available drives in PowerShell. you can see that three drives come with the SQL Server PowerShell provider: SQLSERVER, SQL, and SQLPolicy.

SQL Server PowerShell showing the results of a Get-PSDrive statement

SQL Server PowerShell showing the results of a Get-PSDrive statement

The SQLSERVER drive is considered the root path, and beneath it are two objects: SQLPolicy and SQL. SQLPolicy is used to navigate and manage Policy Management framework objects. SQL is used to navigate SQL Server objects via the SMO hierarchy.

Using the familiar commands CD and DIR, you can easily navigate your way around the database objects. For example, to enumerate the AdventureWorks table list.

CD DatabasesAdventureworks

and then this:

DIR Tables

Remember that the CD and DIR statements are not actual PowerShell commands. Rather, they are aliases to Set-Location and Get-ChildItem, respectively.

The real power of PowerShell comes with its seamless integration across multiple applications and the operating system itself. Currently, Microsoft Exchange Server 2007 and SQL Server 2008 have native providers for PowerShell. This allows you to easily obtain information within a script across these various platforms. To help illustrate this concept, let’s create a simple script that obtains job information from the Windows Job Scheduler service as well as SQL Server Agent jobs, and insert this information into a table to be used for reporting. The following is the code for this example:

#Use WMI to obtain the AT Scheduler job list
$colItems = get-wmiobject -class "Win32_ScheduledJob"
-namespace "rootCIMV2" -computername "." foreach ($objItem in $colItems)
$JobId = $objItem.JobID
$JobStatus = $objItem.JobStatus
$JobName = $objItem.Command
#Use the SQL Provider Invoke-SqlCmd cmdlet to insert result into JobReports table
Invoke-SqlCmd -Query "INSERT INTO master.. JobReports (job_engine, job_engine_id,
job_name, job_last_outcome) VALUES('NT', '$JobId','$JobName','$JobStatus')"
#Now let's obtain the job listing from the JobServer object
Set-Location "SQL:epgopsr2basedefaultJobServer"
$jobItems = get-childitem "Jobs"
foreach ($objItem in $jobItems)
$JobId = $objItem.JobID
$JobStatus = $objItem.LastRunOutcome
$JobName = $objItem.Name
Invoke-SqlCmd -Query "INSERT INTO master.. JobReports (job_engine,
job_engine_id, job_name, job_last_outcome) VALUES('AGENT','$JobId',

This example assumes we have a table in the master database, defined as follows:

(job_engine CHAR(6),
job_engine_id VARCHAR(50),
job_name VARCHAR(255),
job_last_outcome VARCHAR(50),
report_time datetime DEFAULT GETDATE())

After running our code, the JobReports table would be filled with entries of both the Windows AT scheduled jobs and SQL Server Agent jobs.

SQL Server Agent jobs

In the real world, we would probably want to add much more information about the jobs, like their run times, and perhaps add the ability to obtain this information across all job servers in the enterprise.However, the main point of this example is to show how we can seamlessly obtain information from a variety of sources.

The sample script obtains Agent job information by enumerating jobs within the jobs container. The properties exposed here are the same properties you would find from the Jobs object in the Microsoft.SQLServer.Management.SMO.Agent namespace. To see a list of available properties.

Get-Item <object_name> | get-member -MemberType property

where object_name is the name of a job in our example. This command works with any object in PowerShell. Issuing this command against one of our Agent jobs yields:

command works with any object in PowerShell

command works with any object in PowerShell

Five cmdlets are currently available in the SQL Server PowerShell provider:

  • Decode-SqlName and Encode-SqlName decode and encode special characters in SQL Server names to formats usable in PowerShell.
  • Convert-UrnToPath is a useful cmdlet when coverting an SMO Uniform Resource Name (URN) value into a valid PowerShell path format.
  • Evaluate-Policy is used to evaluate a Policy Management policy against a particular server instance.
  • Invoke-Sqlcmd is essentially a call to the SQLCMD executable in-line within the code. We used this cmdlet in our example.

Once you have a useful script, you can run it on demand by calling it from the SQL PowerShell command line, or you can use SQL Server Agent to run it. In SQL Server 2008, Agent has a new subsystem called PowerShell. This subsystem allows users to create job steps of type PowerShell. Having a PowerShell Agent job step allows users to run PowerShell scripts as part of their jobs.

After reading about SQL Server PowerShell, you may be wondering why you should bother learning PowerShell if a T-SQL script can handle your current situation. Although T-SQL may work for you, SQL PowerShell gives you additional capabilities that may benefit you in the long term. For example, SQL PowerShell provides access to all of the capabilities of SMO, the registry, registered servers, and the Policy Management framework. Also, users of Windows or Exchange will find it really easy to integrate SQL within their management scripts. Microsoft is making a big bet with PowerShell as the future scripting engine of Windows.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics