Tip

Creating Windows PowerShell scripts to manage SQL Server 2008 instances

If you wrote Windows PowerShell scripts to manage a SQL Server instance and its databases prior to SQL Server 2008, you would have to define objects based on the SQL

    Requires Free Membership to View

Server Management Object (SMO) model to access various server and database components. But with the release of SQL Server 2008, accessing these components is now simply a matter of maneuvering through the server/database hierarchy as if it were a file directory.

In SQL Server 2008, PowerShell support still relies on the SMO model, but the work is done through the use of two PowerShell snap-ins that ship with SQL Server 2008. The ServerProviderSnapin100 snap-in lets you connect to a SQL Server instance and maneuver through the SQL Server component hierarchy, and the SqlServerCmdletSnapin100 snap-in provides a set of cmdlets (PowerShell commands -pronounced com_MAND--let) specific to SQL Server. The combination of the snap-ins and the PowerShell environment make it easy to create PowerShell scripts that automate the management of a SQL Server instance.

To demonstrate how easy it is to create and use PowerShell scripts, this tip explains a basic script that retrieves information about the databases on a specific SQL Server instance. It takes you through each line of the script and then explains how to run the script in PowerShell. Although this discussion focuses on a single script, these concepts apply to many scripts that help manage SQL Server. For more details on how SQL Server support has been implemented in PowerShell, see SQL Server 2008 Books Online.

Creating the PowerShell Script
A PowerShell script is a text file saved with a .ps1 extension. The file name of the script in this article is DbInfo.ps1, and this script retrieves the name, size, space usage and space available for all nonsystem databases on an instance of SQL Server. The database information retrieved from SQL Server is then outputted to a .csv file. The following code shows the contents of the script file:

# define parameters
param
(
$Server = $(Read-Host "Server"),
$Instance = $(Read-Host "Instance (use 'default' for default instance)"),
$TargetFolder = $(Read-Host "Target folder")
)

# if necessary, add backslash to target folder
If (!$TargetFolder.EndsWith("\"))
{
$TargetFolder = $TargetFolder + "\"
}

# set path and file name for output file
$FileName = $TargetFolder + $Server + "." + $Instance + ".csv"

# retrieve database objects
$Path = "sqlserver:\sql\$Server\$Instance\Databases"
$Databases = Get-ChildItem $Path

# return details about each database
$Databases |
Select-Object Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable |
Export-Csv -path $FileName

The first section of the PowerShell script defines the parameters that are passed into the script file when it is called. As shown in the following code, there are three parameters:

param
(
$Server = $(Read-Host "Server"),
$Instance = $(Read-Host "Instance (use 'default' for default instance)"),
$TargetFolder = $(Read-Host "Target folder")
)

The $Server parameter is the computer where SQL Server is installed. If the computer is the local system, you can specify "localhost" when prompted rather than the computer name. The $Instance parameter is the specific SQL Server instance. If this is the default instance, you must specify "default." The third parameter, $TargetFolder, is the folder where the .csv output file will be created.

As you can see, the param statement defines the necessary parameters. The parameter definitions themselves are enclosed in parentheses, and if there is more than one, they are separated by commas. Each parameter is set by providing a value for that parameter. The value is based on the Read-Host cmdlet, which displays a string value and prompts the user for input.

The Read-Host cmdlet for the $Server parameter, for example, displays the word server and takes the SQL Server computer name as input. The Read-Host cmdlet and its arguments are enclosed in parentheses and preceded by a dollar sign. This informs PowerShell to process this expression first and then assign the value to the $Server parameter. As a result, the server name entered by the user is assigned to $Server.

The next section of the script is an if statement, which ensures that the $TargetFolder value ends with abackslash used to concatenate the file name later in the script:

If (!$TargetFolder.EndsWith("\"))
{
$TargetFolder = $TargetFolder + "\"
}

An if statement begins with the if keyword followed by an expression, which is enclosed in parentheses and that is evaluated when the if statement is executed. If the expression evaluates to true, the code within the script block, enclosed in curly brackets, runs. Otherwise, the script block does not run.

In this example, the expression being evaluated uses the $TargetFolder variable's EndsWith method to determine whether the specified file path ends with a backslash. Because an exclamation point precedes the expression, the expression evaluates to true only when the file path does not end with a backslash. When the expression evaluates to true, the script block runs and adds a backslash to the variable value.

The next line of code in the PowerShell script concatenates variable and string values to create the file path and name of the output file for the database information:

$FileName = $TargetFolder + $Server + "." + $Instance + ".csv"

As you can see, the file name is based on the server and instance names. If the SQL Server computer is named Server01 and the SQL Server instance is SqlSrv2008, for example, the file name is Server01.SqlSrv2008.csv. This value is then assigned to the $FileName variable.

This PowerShell script attempts to define the path that points to the databases in a specific SQL Server instance. When you use PowerShell for SQL Server objects such as databases, you must identify the hierarchy in which these objects are situated, as shown in the following code:

$Path = "sqlserver:\sql\$Server\$Instance\Databases"

You may notice that the path looks similar to a file path in a directory structure. First, you must specify the correct provider (sqlserver:\sql). This tells PowerShell that the path describes a location in an instance of SQL Server.

The next part of the path is the server name, which has been saved in the $Server variable after being passed in as a parameter at the beginning of the script. After you specify the server, you must specify the instance. In this case, you do so with the $Instance variable. The next layer of the hierarchy contains the databases themselves, which are located within the Databases collection. The collection includes an object for each database within the collection. In other words, every SQL Server instance has a collection of databases, and each of these databases is represented as an object in PowerShell.

When I define the path in my PowerShell script, I treat it as a string by enclosing it in quotes and then assign it to the $Path variable. I can then use the $Path variable to retrieve the databases from the Databases collection, as shown in the following line of code:

$Databases = Get-ChildItem $Path

The Get-ChildItem cmdlet retrieves the objects contained within a container in a similar fashion to how you would retrieve a set of files from a directory. When calling the Get-ChildItem cmdlet, the $Path variable is passed as an argument. As a result, all databases contained in the Databases collection of the specified SQL Server instance will be returned. The collection of databases is then assigned to the $Databases variable.

After you've assigned your collection of databases to a variable, you can use that variable to access the individual databases within the collection. In the next section of the script, I pipe, or send, the database collection to the Select-Object cmdlet:

$Databases |
Select-Object Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable |
Export-Csv -path $FileName

Notice that a vertical pipe (|) follows the $Databases variable. This tells PowerShell to pipe the data returned by one command down to the next command. In this case, the contents of the $Databases variable are sent down the pipeline to the Select-Object cmdlet. The Select-Object cmdlet then retrieves the specified property values associated with each database object passed down to it. If AdventureWorks is one of the databases on the specified SQL Server instance, for example, the name, size, data space usage and index space usage for that database will be returned by the command. These property values are then piped to the Export-Csv cmdlet, which outputs the database property values to the .csv file.

You can also return the property values for any properties supported by the database object. To view a list of these properties, use the Get-Member cmdlet. This enables you to retrieve an object's methods and properties. The following statement, for example, retrieves a list of properties for the database object associated with the AdventureWorks database:

$Databases | Where-Object{$_.Name –eq 'AdventureWorks'} |

The statement begins by calling the $Databases variable and ends by passing those values down the pipeline. This process assumes that you've already populated the variable with a collection of databases from a specific instance of SQL Server.

The next command in the pipeline is a Where-Object cmdlet. The cmdlet includes an expression, enclosed in curly brackets, that is used to evaluate each object in the pipeline. If the expression evaluates to true, the object is passed down the pipeline to the next command. In the statement above, for example, the value of the Name property must equal "AdventureWorks" for the expression to evaluate to true. As a result, only the AdventureWorks database object is passed down the pipeline to the Get-Member cmdlet. In this case, I specify the -MemberType argument and the "property" value so that only the properties are returned.

Running the PowerShell Script

After you've created your PowerShell script file, you can call the file from the PowerShell console and pass in the necessary parameter values. The simplest way to open PowerShell and interface with SQL Server is to enter the following command in the Run command window:

sqlps

When you run the command, PowerShell will open, automatically load the necessary SQL Server snap-ins and display the SQLSERVER:\ prompt. This prompt indicates that the SQL Server provider is both loaded and the active location, similar to a file directory prompt like C:\. Another option is to open the PowerShell console and then enter the sqlps command.

At the SQLSERVER:\ prompt, you can submit any ad hoc PowerShell commands that are supported by PowerShell and the SQL Server provider. You can also run a PowerShell script at the prompt. Before you run a script, however, the execution policy must be configured to permit script execution. One method you can use to set the execution policy is to run the following command in the PowerShell console:

Set-ExecutionPolicy RemoteSigned

As its name indicates, the Set-ExecutionPolicy cmdlet sets PowerShell's execution policy. The RemoteSigned argument indicates that any scripts downloaded from the Internet must be digitally signed. You can, however, run scripts that you created locally. To set the execution policy, you must run PowerShell as an administrator. It is also helpful to research PowerShell's execution policies beforehand. For details on setting Powershell's execution policy, run the following command at the PowerShell command prompt:

Help Set-ExecutionPolicy –Detailed

After you set the correct execution policy, you can run your script. To run the script, enter the path and file name at the PowerShell command prompt. You don't need to specify the .ps1 file extension. If the DbInfo.ps1 file is in the C:\ScriptFiles directory, for example, you need to enter only the following command at the PowerShell prompt:

C:\ScriptFiles\DbInfo

When you enter the command, you will then be prompted for the server and instance names as well as for the target folder for the output file. The script will then run and generate a .csv file that includes information about each user-defined database on the specified SQL Server instance.

You can also run the script at the PowerShell prompt without having to specifically use the sqlps command to switch to the SQL Server provider. But if you do so, you must load the SQL Server snap-ins before running any scripts that contain SQL Server commands. To load the snap-ins, run the following commands at the PowerShell prompt:

Add-PsSnapin SqlServerProviderSnapin100
Add-PsSnapin SqlServerCmdletSnapin100

The Add-PsSnapin cmdlet adds the providers that are loaded on your system when you install SQL Server 2008.

With this overview of PowerShell support in SQL Server 2008, you can create scripts that make repetitive tasks faster and easier. While the DbInfo.ps1 example script scratches only the surface of PowerShell's possibilities, it demonstrates the basic concepts for writing more complex scripts.. Be sure to check out SQL Server 2008 Books Online and PowerShell's online help files for more information about working with PowerShell and using PowerShell scripts to interface with instances of SQL Server.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.


This was first published in January 2009

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.