Solving a SQL Server administration task with PowerShell scripts often requires the use of SQL Server Management Objects (SMO). For a DBA new to PowerShell scripting this presents a doubly step learn curve first you must learn the PowerShell language and then you must learn the SMO objects necessary to accomplish your task. There are many wonderful books and tutorials available for

learning PowerShell.

A good starting point is the Windows PowerShell site.

For learning SMO objects, the goal of the CodePlex project SQL Server PowerShell Extensions (SQLPSX)

is to provide intuitive PowerShell functions around SMO objects to make working with SMO

from PowerShell easier. This article is not intended to be an introduction to

PowerShell, but rather an introduction to SQLPSX. A basic understanding of

PowerShell is helpful, but not necessary to follow the examples.

Getting Started

• Install SMO which is included with SQL Server Management Studio
• Install PowerShell
• Set your PowerShell execution policy to remotesigned. Launch PowerShell and type Set-ExecutionPolicy RemoteSigned Note: If you are running Vista, follow these instructions
• Download SQL Server PowerShell Extensions (SQLPSX)
• Unblock the SQLPSX PowerShell scripts. Right click each .ps1 file and click Unblock

  as shown below:

Launch PowerShell and source or dot the LibrarySmo.ps1 file using the

following command:

. .\LibrarySmo.ps1

The LibrarySmo.ps1 file contains a library a functions and sourcing a

library file simply loads the definitions of the functions but does not execute

the functions.

Note: That is dot space dot. You can verify that the library is sourced with the

following:

Get-Command *et-Sql* | Select Name

As you can see, you should have several new functions:

The readme.txt file included in the releases section of SQLPSX contains the documentation of each function included below for your reference. A brief description of each is below:

* = Helper functions called by other functions and not used directly. Unfortunately in PowerShell 1.0 there isn't a way to make these functions private

Additional properties have been added to the base SMO objects where

appropriate. One special property is the members property, whenever permissions,

role membership or user/login information is listed the members property shows

the effective members within the context of the object. For example, the members

property of Get-SqlUser shows all of the members of a Windows group including

nested groups and the members property of Get-SqlDatabaseRole lists every member

of the role both direct and indirect i.e. enumerates Windows groups and other

Database Roles. Likewise for permission type functions (Get-SqlDatabasePermission,

Get-SqlObjectPermission, and Get-SqlServerPermission) the members property shows

the effective grantee of the assigned permission. The current list of extended

properties include:

Using SQLPSX

Get-SqlServer

Now that we have our functions sourced will  look at a few examples. To

assign a SMO Server object to the variable $server From

the PowerShell prompt run

the command:
$server = Get-SqlServer 'Z002\SqlExpress'

To see all of the methods and properties available for the $server variable pipe

the variable to the PowerShell built-in cmdlet Get-Member as shown here:

$server | Get-Member

Notice the EnumProcesses method, to call this method and see the

output formatted as a table, call the EnumProcesses() method of the $server

variable and pipe the output to the Format-Table cmdlet:

$server.EnumProcesses() | Format-Table

To see information about the SQL Server:
$server.Information

You can also drill into the Information property, for example to see the SQL Server version run

the command:
$server.Information.VersionString

Get-SqlDatabase

Get-SqlDatabase has one required parameter, a SQL Server and an optional parameter of a database and returns either a single SMO Database object if a database is specified or a collection of SMO Database objects for all the databases on the SQL Server

instance if the SQL Server is specified without a database. To create and assign a SMO database object to the variable $db and then see the available properties run

the following command:
$db = Get-SqlDatabase 'Z002\SqlExpress' AdventureWorks
$db | Get-Member -type Property

We can use the Tables property of our Database object which returns a collection SMO Table objects

to list the Name, RowCount and DataSpaceUsed properties for each table and sort the output by DataSpaceUsed in descending order:

$db.Tables | select name, RowCount, DataSpaceUsed | Sort-Object -property DataSpaceUsed -descending

Get-SqlData

Get-SqlData executes a query against the specified server and database returning an ADO.NET DataTable.

To select all the records from the authors table:

$dt = Get-SqlData 'Z002\SqlExpress' pubs 'SELECT * FROM dbo.authors'

Now that we have a ADO.NET DataTable object we can pipe the output through PowerShell's Where cmdlet

to retrieve specific rows with the au_lname column equal to 'White':
$dt | where { $_.au_lname -eq 'White' }

Set-SqlData

Set-SqlData is very similar to Get-SqlData, and is used where you need

to execute a query with no results. The following is an example which imports a

csv file called "authors.csv" into the authors table in the pubs database:
Set-SqlData 'Z002\SqlExpress' pubs "BULK INSERT pubs..authors FROM 'c:\authors.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

Get-SqlUser

Get-SqlUser has a required parameter a SMO Database object. The

following is an example which first creates and assigns a SMO Database object to the variable $db and passes the variable as a parameter to Get-SqlUser:

$db = Get-SqlDatabase 'Z002\SqlExpress' AdventureWorks
Get-SqlUser $db

Get-SqlDatabaseRole

Get-SqlDatabaseRole has a required parameter of a SMO Database object.

As you can see the pattern for calling Get-SqlDatabaseRole is identical to

Get-SqlUser:

$db = Get-SqlDatabase 'Z002\SqlExpress' AdventureWorks
Get-SqlDatabaseRole $db

Get-SqlLogin

Get-SqlLogin has a required parameter of SQL Server which can be either a SQL Server SMO object or the SQL Server name.

The following is an example

Get-SqlLogin 'Z002\SqlExpress'

Get-SqlLinkedServerLogin

Get-SqlLinkedServerLogin has a required parameter of SQL Server which can be either a SQL Server SMO object or the SQL Server name.

The following example is an example:
Get-SqlLinkedServerLogin 'Z002\SqlExpress'

Get-SqlServerRole

Get-SqlServer Role has a required parameter of SQL Server which can be either a SQL Server SMO object or the SQL Server name.

The following is an example

Get-SqlServerRole 'Z002\SqlExpress'

Get-SqlServerPermission

Get-SqlServerPermission has a required parameter of SQL Server. The

following is an example:
Get-SqlServerPermission 'Z002\SqlExpress'

Get-SqlDatabasePermission

Get-SqlDatabasePermission has a required parameter of a SMO Database

object. The following is an example:
$db = Get-SqlDatabase 'Z002\SqlExpress' pubs
Get-SqlDatabasePermission $db

Get-SqlObjectPermission

Get-SqlDatabasePermission takes a required parameter of a SMO Database

object. The following is an example:
$db = Get-SqlDatabase 'Z002\SqlExpress' pubs
Get-SqlObjectPermission $db

Piping Get-SqlServer and Get-SqlDatabase

What if you want to know the version number of a list of SQL Servers? The

following is an example

which uses a text file containing a list of SQL Servers:
Get-Content ./servers.txt | foreach { $srvr = Get-SqlServer $_ ; $srvr.Information | add-Member -memberType noteProperty -name Server -value $srvr.Name -passThru | Select Server, VersionString }

This example is little more complex than previous examples, what we've done is

get a list of SQL Servers from the text file servers.txt using the built-in PowerShell cmdlet Get-Content. Next we assign a SMO Server object to the variable $srvr. We then access the Server object Information property $srvr.Information and add a new property with the name

Server. And finally we select only the new Server and VersionString

properties.

The use of Get-Content to retrieve server names from a text file is good

is simple, but we're DBAs, we often store data in SQL Server tables not text files. Here's the same example as before only this time we use the SQLPSX function Get-SqlData to retrieve the server name from a table called SqlServer on

the Z002\SqlExpress server in the SQLPSX database:

Get-SqlData 'Z002\SqlExpress' SQLPSX "SELECT Server FROM dbo.SqlServer" | foreach { $srvr = Get-SqlServer $_.Server ; $srvr.Information | add-Member -memberType noteProperty -name Server -value $srvr.Name -passThru | Select Server, VersionString }

Since the Get-SqlDatabase function when called with only a SQL Server parameter returns a collection of Database objects you can pipe object to other cmdlets or functions to retrieve properties for all databases on the SQL Server:.

To see the database name and size of all databases on the specified SQL Server:

Get-SqlDatabase 'Z002\SqlExpress' | Select name, size

Or to retrieve the Users for each database on SQL Server:

Get-SqlDatabase 'Z002\SqlExpress' | Get-SqlUser

We can also do multiple piping operations, for example to retrieve the Users for each database

from a list of SQL Servers:

Get-Content ./servers.txt | foreach { Get-SqlDatabase $_ | Get-SqlUser }

Next Steps

SQLPSX is a community project hosted on CodePlex and we're looking for feedback

including your thoughts on new functions, scripts and cmdlets, so join the discussion and let us know

what  you'd like to see in a future release.

The second article in this series will  demonstrate importing

security information into a database and reporting against the information using

the SQL Server Reporting Services reports included in SQLPSX.