Solving SQL Server administration tasks with PowerShell scripts can be a daunting task, especially for DBAs who are new to PowerShell scripting. Not only do you need to learn the PowerShell language, but you also need to familiarize yourself with SQL Server Management Objects (SMO) to accomplish your tasks efficiently.

Fortunately, there are tools available that can simplify the process of working with SMO from PowerShell. One such tool is the SQL Server PowerShell Extensions (SQLPSX) project, which provides intuitive PowerShell functions around SMO objects to make your life easier.

In this article, we will not provide an introduction to PowerShell itself, but rather focus on introducing SQLPSX and its capabilities. While a basic understanding of PowerShell is helpful, it is not necessary to follow the examples provided.

Getting Started

Before you can start using SQLPSX, you need to perform a few setup steps:

1. Install SQL Server Management Objects (SMO), which is included with SQL Server Management Studio.
2. Install PowerShell.
3. Set your PowerShell execution policy to remotesigned.
4. Download SQL Server PowerShell Extensions (SQLPSX) from the CodePlex project page.
5. Unblock the SQLPSX PowerShell scripts.
6. Launch PowerShell and source the LibrarySmo.ps1 file using the command: . .\LibrarySmo.ps1

Once you have completed these steps, you can verify that the library is sourced by running the command: Get-Command *et-Sql* | Select Name. This will display a list of the new functions available to you.

Using SQLPSX

Now that we have our functions sourced, let’s look at a few examples of how to use them:

Get-SqlServer

To assign a SMO Server object to a variable, you can use the Get-SqlServer function. For example:

$server = Get-SqlServer 'Z002\SqlExpress'

You can then explore the available methods and properties of the $server variable using the Get-Member cmdlet:

$server | Get-Member

Get-SqlDatabase

The Get-SqlDatabase function allows you to retrieve a single SMO Database object or a collection of Database objects for all the databases on a SQL Server instance. For example:

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

You can then view the available properties of the $db variable using the Get-Member cmdlet:

$db | Get-Member -Type Property

Get-SqlData

The Get-SqlData function allows you to execute a query against a specified server and database, returning an ADO.NET DataTable. For example:

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

You can then filter the results using PowerShell’s Where cmdlet. For example, to retrieve specific rows where the au_lname column is equal to ‘White’:

$dt | Where-Object { $_.au_lname -eq 'White' }

Set-SqlData

The Set-SqlData function is similar to Get-SqlData, but is used when you need to execute a query that does not return a result set. For example, you can use it to import a CSV file into a table:

Set-SqlData 'Z002\SqlExpress' pubs "BULK INSERT pubs..authors FROM 'c:\authors.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

These are just a few examples of the functions available in SQLPSX. There are many more functions that can help you with various SQL Server administration tasks, such as managing users, roles, logins, and permissions.

Conclusion

SQLPSX is a powerful tool that can greatly simplify SQL Server administration tasks when using PowerShell scripts. By providing intuitive functions around SMO objects, it eliminates the need to learn the intricacies of SMO and allows you to focus on the task at hand.

If you’re new to PowerShell scripting, it’s recommended to start with learning the basics of PowerShell itself before diving into SQLPSX. Once you have a good understanding of PowerShell, you can leverage the power of SQLPSX to streamline your SQL Server administration tasks.

Remember, SQLPSX is a community project, and feedback is always welcome. If you have any suggestions for new functions, scripts, or cmdlets, be sure to join the discussion and share your thoughts.

Stay tuned for the next article in this series, where we will demonstrate how to import security information into a database and generate reports using SQL Server Reporting Services reports included in SQLPSX.