Using SQL Server Management Objects with PowerShell

Nowadays most Microsoft server products are being shipped with Windows PowerShell cmdlets to manage them. For example, Microsoft Exchange Server, Microsoft SharePoint, and Microsoft Lync have a lot of cmdlets. However, SQL Server has only a handful. In fact, cmdlets don't exist for many of the tasks performed by SQL Server database professionals. For example, there aren't cmdlets for managing the database engine, working with databases and tables, or managing SQL Server Agent jobs.

Fortunately, when there isn't a SQL Server PowerShell cmdlet available, you can use SQL Server Management Objects (SMO). Introduced in SQL Server 2005 as part of the SQL Server Features Pack, SMO is designed for managing SQL Server through programming. The SMO namespace contains a collection of Microsoft .NET Framework classes for the core SQL Server Database Engine objects. Before PowerShell became available, these .NET classes were intended for use in .NET applications built using the Visual Basic .NET or C# language. But since the introduction of PowerShell, it's possible to consume the SMO namespace and produce some amazing scripts using PowerShell.

Learning how to use SMO is a valuable skill to add to your skill set. Understanding the .NET Framework technologies is also valuable. If you ever have to create a SQL Server Common Language Runtime (CLR) function or create a SQL Server Integration Services (SSIS) package that requires using the Script Task component, you need to understand the .NET basics. Knowing the basics even helps with PowerShell, because it's a .NET scripting language. Microsoft has done an excellent job of documenting SMO and the .NET Framework on MSDN. The Learning Path points to some of this documentation.

SMO has a logical layout that most SQL Server database professionals find straightforward and relatively easy to use. I'll walk you through several examples of how to use SMO with PowerShell.

Meeting the Prerequisites

If you're new to PowerShell, I recommend that you use PowerShell 2.0 or later because it includes the Windows PowerShell Integrated Scripting Environment (ISE). This functionality lets you run commands and write, edit, and debug scripts in a graphical environment. Before you can start running commands and writing scripts, though, you need to make sure your PowerShell environment is ready. Here are some pointers on how to get ready if you're using PowerShell 2.0 or later:

Set-ExecutionPolicy RemoteSigned

If you're managing instances that are running different SQL Server versions, I recommend using the latest SMO version. If you don't want to install the full version of SQL Server on a machine but you want to script against a database, you can install the following components from the SQL Server Feature Pack in the order shown:

Creating Your First PowerShell SMO Script

After making sure all the prerequisites are met, you can proceed to create your first PowerShell SMO script. First, you need to open a PowerShell console session and load the SMO assembly that will let you build your SQL Server .NET objects. Here are three ways to load the Microsoft.SqlServer.Smo assembly, depending on your environment:

Method 1. If you're using PowerShell 1.0, you can load a .NET assembly for that PowerShell version using the command:

[System.Reflection.Assembly]::LoadWithPartialName  ("Microsoft.SqlServer.Smo");

(Although this command wraps here, you need to enter it all on one line in the PowerShell console.) This command will grab the latest SMO installed in your system. You can also use this method with PowerShell 2.0 and later.

Method 2. In PowerShell 2.0 and later, you can use the Add-Type cmdlet to load the Microsoft.SqlServer.Smo assembly:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

Unfortunately, this command won't work if you have multiple SQL Server versions installed on your machines. In addition, you might also encounter an error message if you're running it on a single SQL Server 2012 instance. This is a known bug that has been reported to Microsoft.

Method 3. This method is a workaround if you're using PowerShell 2.0 or later but Method 2 doesn't work. In this workaround, you use the full SMO assembly path in the command:

Add-Type -path `"C:WindowsassemblyGAC_MSILMicrosoft.SqlServer.Smo  10.0.0.0__89845dcd8080cc91Microsoft.SqlServer.Smo.dll"

(Although the path in this command wraps here, you need to enter the path all on one line in the PowerShell console.) You can find the code for these three methods in the SMO_Article_Code.ps1 file.

After loading the assembly, you need to create a SQL Server .NET object that connects to a SQL Server instance. You can create this object using the code:

$SQLSvr = "."$MySQLObject = new-object `  Microsoft.SqlServer.Management.Smo.Server `  $SQLSvr;;

This code connects to the local instance using a period (".") and creates a new object named $MySQLObject. Alternatively, you can connect to an instance by specifying the computer name in single or double quotes (e.g., "YourSqlServer1", 'YourSqlServer1') or a full instance name in single or double quotes (e.g., "YourSqlServer1InstanceXX", 'YourSqlServer2InstanceYY').

As the computer name and full instance name examples show, you can use either double or single quotes to enclose strings in PowerShell. However, they're not necessarily interchangeable. The best way to illustrate this is with an example. Suppose you want to store the password $GalaPrem1r! in the variable $MyPassword, then view that password. If you use single quotes to enclose the string ('$GalaPrem1r!') and run the command

$MyPassword = '$GalaPrem1er!'; $MyPassword;

PowerShell returns the correct value of $GalaPrem1er! However, if you use double quotes to enclose the string ("$GalaPrem1r!") and run the command

$MyPassword = "$GalaPrem1er!"; $MyPassword;

PowerShell returns only the exclamation point (!).

Why did PowerShell return different values when you enclosed the password in single quotes ('$GalaPrem1er!') and double quotes ("$GalaPrem1er!")? When single quotes are used to enclose a string, PowerShell ignores any special characters in that string, including the special characters reserved for PowerShell. In this example, it ignored the dollar sign ($) in $GalaPrem1er!. In PowerShell, $ is a special character used as a prefix to identify variable names. When double quotes are used to enclose a string, PowerShell doesn't ignore any special characters in that string. So, when double quotes were used to enclose $GalaPrem1er!, PowerShell read $GalaPrem1er as a variable name instead of a string and just returned !. As this example illustrates, you need to pay close attention to how you build and use strings.

At this point, you're connected to your SQL Server instance and can retrieve information from it. But how do you access the $MySQLObject SQL Server object information? Here's where SMO's magic begins to pay off.

SMO's magic begins to pay off

To see what's in $MySQLObject, you need to be familiar with the PowerShell cmdlet Get-Member, which has the alias gm. You can see the documentation for this cmdlet by running the following command in the PowerShell console:

Get-Help Get-Member –full;

Once you're familiar with Get-Member, execute it with $MySQLObject in the following way:

$MySQLObject | gm | more;

This command displays all of the .NET objects stored in your PowerShell variable—in this case, all of your SQL Server instance objects. You'll see all the .NET object properties and methods. You can think of a property as container holding one or more static values and a method as a function that changes the behavior of an object.

This type of command is often referred to as a "one-liner." The horizontal bar (|) is the known as a pipe, and its purpose is to pass values. In this case, the first pipe is passing the .NET object in $MySQLObject to the Get-Member cmdlet. The second pipe is passing the Get-Member cmdlet's output to the More command. As Figure 1 shows, the More command limits the display of information to one screen at a time and adds the More prompt so that you can scroll through the results.

Figure 1: Reviewing the .NET Object's Properties and Methods One Screen at a Time

The semicolon (;) is the line terminator, which is optional.

If you don't want to clutter your console, you can change the More command to the Out-Gridview cmdlet:

$MySQLObject | gm | Out-Gridview;

As Figure 2 shows, the results are displayed in an interactive table located within a grid view window. Using either the More command or the Out-Gridview cmdlet is acceptable, so you can use which one best suites your needs.

Figure 2: Reviewing the .NET Object's Properties and Methods in an Interactive Table

After looking through the properties and methods, suppose that you want to display the values in the Information property. To do so, you'd use the command:

$MySQLObject.Information;

As you can see in Figure 3, you just opened the box to wealth of information about your SQL Server instance with only a few lines of code.

Figure 3: Retrieving All the Information from a SQL Server Instance

If you want to select only certain pieces of information and format them in a table, you can use the PowerShell cmdlets Select-Object (which has the alias select) and Format-Table (which has the alias FT). For example, you can use the following command to display just the Parent, Product, Edition, and VersionString values in a table:

$MySQLObject.Information `  | Select Parent, Product, Edition, VersionString `  | FT -auto;

The Format-Table cmdlet's -auto parameter automatically adjusts the column size and number of columns based on the width of the data. Figure 4 shows the results.

Figure 4: Retrieving Specific Information from a SQL Server Instance

Retrieving Information from Multiple SQL Server Instances

So far, you've seen how to retrieve information from one SQL Server instance. Now I'll show you how to get information from multiple SQL Server instances.

Listing 1 provides a sample script that demonstrates how you can use SMO to get a list of all your SQL Server instances and retrieve specific information from each one. (SMO_Article_Code.ps1 includes Listing 1 and the other listings presented here.)

## Load the .NET assembly.[System.Reflection.Assembly]::LoadWithPartialName( `  "Microsoft.SqlServer.Smo");## Build the list of SQL Server instances.$SQLSvr = `  [Microsoft.SqlServer.Management.Smo.SmoApplication] `  ::EnumAvailableSqlServers($false) | Select name;$SQLSvr;## For each SQL Server instance, display some information.foreach($svr in $SQLSvr){   ## Build the SQL Server .NET object.   $MySQLObject = `   new-object Microsoft.SqlServer.Management.Smo.Server `$svr.Name;## Work with SMO and the databases.$MySQLObject.Information | `  Select Parent, Product, Edition, VersionString `  | FT -auto;};

To obtain the list of all instances, the script uses the EnumAvailableSqlServers() method of the SmoApplication class, which is part of the Microsoft.SqlServer.Management.Smo namespace. The script then uses PowerShell's Foreach statement to iterate through the list, retrieving the specified information for each instance. Figure 5 shows sample results from this script.

Figure 5: Retrieving Specific Information from All SQL Server Instances

Listing 2 presents a variation of the previous script. Instead of collecting information from all the SQL Server instances, the script in Listing 2 collects information from the instances specified in a list.

## Load the .NET assembly.[System.Reflection.Assembly]::LoadWithPartialName( `  "Microsoft.SqlServer.Smo");## Provide the list of SQL Server names.$SQLSvr = "BIZTALK2K10DEV","WIN2K3SQL1","WIN1PROUS01";## For each SQL Server instance, display some information## and save it in a PowerShell variable.$Results = $null;$Results = foreach($svr in $SQLSvr){  ## Build the SQL Server .NET object.  $MySQLObject = `    new-object Microsoft.SqlServer.Management.Smo.Server `    $svr;  ## Work with SMO and the databases.  $MySQLObject.Information | `    Select Parent, Product, Edition, VersionString;};## Display the results on screen.$Results | ft -autosize;

The information from each server is stored in a variable. Its contents are displayed at the end. Figure 6 shows sample results from this script.

Figure 6: Retrieving Specific Information from the Listed SQL Server Instances

Retrieving Information About Databases and SQL Server Agent Jobs

As I mentioned previously, SQL Server doesn't ship with PowerShell cmdlets for working with databases and managing SQL Server Agent jobs. Let's see how to use SMO to work around these omissions.

Listing 3 shows a script that retrieves information about databases.

## Load the .NET assembly.[System.Reflection.Assembly]::LoadWithPartialName( `  "Microsoft.SqlServer.Smo") | Out-Null;## Build the list of SQL Server names.$MySQLObject = `  new-object Microsoft.SqlServer.Management.Smo.Server `  "SQL01";$MySQLObject.databases `  | Select parent, name, Owner, `  RecoveryModel, Status | FT -AutoSize;

Although it's retrieving the values of only three properties (i.e., Owner, Recovery Model, and Status), the $MySQLObject variable will have more properties that you might find relevant to display. Notice that this script uses the Out-Null cmdlet to delete output from the command that loads the .NET assembly. This leads to much cleaner results, as shown in Figure 7.

Figure 7: Retrieving the Owner, Recovery Model, and Status of Each Database

Listing 4 shows a script that retrieves information about SQL Server Agent jobs This script uses object encapsulation—($MySQLObject.JobServer.Job)—which provides a way to navigate through a collection of .NET objects.

## Load the .NET assembly.[System.Reflection.Assembly]::LoadWithPartialName( `  "Microsoft.SqlServer.Smo") | Out-Null;## Build the list of SQL Server names.$MySQLObject = `  new-object Microsoft.SqlServer.Management.Smo.Server `  "SQL01";($MySQLObject.JobServer.jobs) `  | Select  Parent, Name, isEnabled, `  lastRunDate, lastRunOutCome `  | ft -Auto;

In this case, the variable $MySQLObject has a property object named JobServer. JobServer also has property objects, one of which is named Job. Besides providing navigation, you can use object encapsulation to help avoid breaking down the main variable object ($MySQLObject) into more unwanted variables. The use of parentheses is optional, but they make it clear that you're encapsulating the object.

As Figure 8 shows, the script in Listing 4 retrieves only a few of the available properties for the Job property object.

Figure 8: Retrieving Basic Information About SQL Server Agent Jobs

If desired, you can use the Get-Member cmdlet to search for all the available properties and methods:

($MySQLObject.JobServer.Job) | Get-Member;

After you decide on which properties you want to display in the console, you can use the Select-Object cmdlet to retrieve those properties.

Gain a New Skill Set

Using SMO with PowerShell gives you the ability to create scripts to document and manage your SQL Server instances. Just about any manual process can be converted to an automated PowerShell task. You can even use Windows Task Scheduler or SQL Server Agent to schedule your scripts to run. Learning to use PowerShell with SMO will give you a new skill set, helping you become a better SQL Server database professional.