PowerShell the SQL Server Way

Although Windows PowerShell has been available to IT professionals going on seven years, there are still many IT pros who are just now deciding to see what the fuss is all about. Depending on your job, you might find PowerShell an invaluable tool.

Microsoft's plan is that PowerShell will be the management tool for all of its servers and platforms. For most IT pros, it's not a matter of if you'll be using PowerShell, only a matter of when.

Related Articles:
Using SQL Server Management Objects with PowerShell
Why PowerShell for SQL Server?

For SQL Server pros, PowerShell has been something of a mystery. I want to introduce you to the world of PowerShell from a SQL Server perspective. Naturally, I can't teach you everything you need to know about PowerShell in a few thousand words, but I can demonstrate how PowerShell and SQL Server can play together and perhaps that will be enough to get the ball rolling.

Before I begin, let me be clear: I'm not advocating replacing T-SQL with PowerShell. If you're currently managing SQL Server with T-SQL, there's no reason to change. But many SQL Server pros are finding ways to incorporate PowerShell into their daily work. For me, the real strength is being able to leverage PowerShell and T-SQL.

Getting Started

I suspect that you're running a desktop or server that has PowerShell already installed, because PowerShell has been part of the OS since Windows 7 and Windows Server 2008 R2. With the release of Windows 8 and Windows Server 2012, Microsoft introduced PowerShell 3.0, but I'm not really going to touch on any of its specific features. I'm going to use the PowerShell bits that are included with SQL Server 2012, because they offer more functionality than the PowerShell snap-ins introduced in SQL Server 2008.

You might have seen the Start PowerShell context menu option in SQL Server Management Studio (SSMS). Personally, I've never found much use for this. The PowerShell session you get is referred to as a mini-shell. In earlier versions of SQL Server, it provided only a subset of PowerShell's functionality, which frustrated many people because they were expecting a full PowerShell experience. The mini-shell in SQL Server 2012 isn't as limited as in previous versions, but my feeling is that if I'm already in SSMS, why do I need a PowerShell session?

For the following demonstrations, I'll be using SQL Server 2012 on a Windows Server 2008 R2 server with PowerShell 3.0 installed. I'm going to run all my commands on the server, but it would be just as easy to use PowerShell remoting from my desktop. (You must have remoting enabled in your environment to do this.)

For SQL Server 2012, which includes PowerShell 2.0 by default, you need to import the sqlps module, which contains all the PowerShell commands related to SQL Server. Before you import it, though, your execution policy must first be configured to run scripts. At a minimum, run this command in an elevated session:

PS C:> set-executionpolicy remotesigned -force

You only have to set this policy once. Note that when entering this command, you shouldn't type the PS C:> portion, because it's not part of the command. Rather, it's the command prompt showing you the current directory in which you should be working. When learning PowerShell, it's helpful to include that information, which is why I'm showing it here and in the commands that follow.

Next, run the following command to import the sqlps module:

PS C:> import-module sqlps

When you import the module, you'll probably get a warning about invalid verbs. All PowerShell commands, which are called cmdlets, are supposed to follow a standard verb-noun naming convention. The verb is from a list of standard .NET verbs such as Get, Set, and Remove. Having a standard naming convention makes it easier to find commands. The sqlps module has a few cmdlets that don't follow this convention. All this means is that it can be a little trickier to discover one of these imported SQL commands.

To see the cmdlets in the module, run this command at the prompt:

PS C:> get-command -module sqlps

You should get a list similar to the one shown in Figure 1.

Figure 1: Getting a List of the Available Cmdlets in the sqlps Module

Note that in PowerShell 3.0, you can simply run a cmdlet that's in the module, such as Restore-SQLDatabase, without importing the module first. In PowerShell 2.0 you need to import the sqlps module first, as I did a moment ago, before you can use any of its commands.

As you can see from most of the cmdlet names in Figure 1, the standard verb-noun naming convention makes it pretty easy to figure out what the command will do. But if you aren't sure, you can ask PowerShell for help. For example, to find out what the Invoke-SQLCmd does, you can run the command:

PS C:> help invoke-sqlcmd

As Figure 2 shows, this command returns information about what the Invoke-SQLCmd cmdlet will do and how to use it.

Figure 2: Getting Help for the Invoke-SQLCmd Cmdlet

If you need more information, you can add the -full parameter, like this:

PS C:> help invoke-sqlcmd -full

This command will return everything, including parameter details and examples. Some cmdlets include a link to an online Help file, which will give you the most up-to-date information. Assuming there's an online link, you can run a command like this:

PS C:> Help get-eventlog -online

Some online pages even have community-contributed content.

Using the SQLServer Provider

In addition to cmdlets, PowerShell supports a software element referred to as a provider. A provider typically provides a management interface to an underlying technology. For example, out of the box PowerShell includes providers for interacting with the file system, registry, and certificate store. Some PowerShell modules and snap-ins include additional providers such as Active Directory. You can view the installed providers with the command:

PS C:> get-psprovider

The sqlps module includes the SQLServer provider. To get information about the SQLServer provider, you can run the command:

PS C:> help sqlserver

Figure 3 shows the results.

Figure 3: Getting Help for the SQLServer Provider

I recommend that you run this command and take a few minutes to read the information about the SQLServer provider. The bottom line is that you can use this provider to navigate a SQL Server instance like it was a file system and use PowerShell's object approach to retrieve information or make some changes. You use the Set-Location cmdlet to change the current directory and the Get-ChildItem cmdlet to list the files and directories contained in the current directory. If you're familiar with the Windows command shell (cmd.exe) commands, you might want to use the cmdlets' aliases of cd and dir, respectively. So, for example, if you want to change to the SQLServer drive and lists its contents, you'd run the commands:

PS C:> cd sqlserver:PS SQLSERVER:> dir

Similarly, you can access your SQL Server instance and navigate through its contents. For example, if your SQL Server instance name is chi-db01, you'd run the commands:

PS C:> cd sqlchi-db01defaultPS SQLSERVER:sqlchi-db01default> dir

Figure 4 shows the results of both sets of commands.

Figure 4: Listing the PSDrives and Their Contents

After you see the results, you can drill down further. For example, as you can see in Figure 4, one of the folders is Logins. To see a list of the login accounts, you can run the command:

PS SQLSERVER:sqlchi-db01default> dir logins

The results are shown in Figure 5.

Figure 5: Listing the Login Accounts

The login accounts are objects, which means you can use other PowerShell cmdlets to display additional information about them. For example, to see more information about the two Globomantics login accounts, you can send or pipe (|) the dir command's results to a where clause, like this:

PS SQLSERVER:sqlchi-db01default> dir logins |  where {$_.name -match "^Globomantics"} |  Select Name,CreateDate,DatelastModified,DefaultDatabase |  format-list

(Although this command wraps here, you would enter it all on one line in the PowerShell console. The same holds true for the other commands that wrap.) As you can see in Figure 6, this one command lists all the login accounts from the Globomantics domain as well as when each login account was created, when it was last modified, and its default database—all formatted in an easy-to-read list.

Figure 6: Obtaining Information About the Globomantics Login Accounts

If the expression in this command looks confusing, don't worry. With a little experience, you'll be able to understand it with no problem. The point I'd like you to take away is what I accomplished with a basic PowerShell expression.

You can use PowerShell's rich language and tool set to obtain a lot more information. For example, if you're running PowerShell with the PowerShell Integrated Scripting Environment (ISE), you can check out the databases on the chi-db01 instance by running a command like this:

PS SQLSERVER:sqlchi-db01default> dir databases |  sort Size |  select Name,CreateDate,Status,RecoveryModel,Size,Owner |  out-gridview

The PowerShell ISE is needed because the command uses the Out-GridView cmdlet to send the output to an interactive table in a separate window, as shown in Figure 7. As you can see, the table lists the databases, sorted by size, and displays some other information about them.

Figure 7: Listing the Databases, Sorted by Size, in an Interactive Table

You can also make changes to items using PowerShell's object model. As an example, I'll walk you through changing the number of allowed account retry attempts (i.e., changing the AccountRetryAttempts property) for Database Mail. First, you need to change to the Mail directory on the chi-db01 instance and list its contents:

PS SQLSERVER:sqlchi-db01default> cd mailPS SQLSERVER:sqlchi-db01defaultmail> dir

In the contents, you'll find the ConfigurationValues object. By piping the results of the dir ConfigurationValues command to the Get-Member cmdlet, you can get the names and descriptions of that object's properties and methods. The command looks like this:

PS SQLSERVER:sqlchi-db01defaultmail>  dir ConfigurationValues | get-member

Figure 8 shows the results.

Figure 8: Getting the Names and Descriptions of the ConfigurationValues Object's Properties and Methods

Based on the information returned, you can determine which properties you want to use. In this case, you'd select the Name and Value properties and use them in the command:

PS SQLSERVER:sqlchi-db01defaultmail>  dir ConfigurationValues | Select Name,Value

Figure 9 shows the results. As you can see, the number of allowed account retry attempts is currently set to 1.

Figure 9: Retrieving the Current Value of the AccountRetryAttempts Property

You'll pipe objects a lot in the SQLServer drive to discover how to use them. To make this easier, you can save an object to a variable. To save the ConfigurationValues object to a variable named $config, you'd run the command:

$config = dir ConfigurationValues

The $config variable is an array of configuration objects whose values you can get and set. Because it's a zero-based array and the AccountRetryAttempts property is the first key in it, you can use $config[0] to get and set its value. For example, the following command retrieves the AccountRetryAttempts property's current value (i.e., 1):

PS SQLSERVER:sqlchi-db01defaultmail> $config[0].value

This command sets its value to 3:

PS SQLSERVER:sqlchi-db01defaultmail> $config[0].value=3

To make sure it's been changed, you can use the Get-Item cmdlet in the command:

PS SQLSERVER:sqlchi-db01defaultmail>  get-item ConfigurationValuesAccountRetryAttempts |  select Name,Value

Figure 10 shows the results. As you can see, the number of allowed account retry attempts has been changed to 3.

Figure 10: Making Sure the AccountRetryAttempts Property's Value Has Been Changed

I'll admit that it can be a little tricky managing SQL Server using the SQLServer provider, as I've been doing here. Thus, when possible, you'll want to take advantage of the sqlps cmdlets because they're easier to use.

Using the sqlps Cmdlets

To use the sqlps cmdlets, you must have the sqlps module installed. The cmdlet I find the most valuable is Invoke-SQLCmd. This cmdlet was first introduced in SQL Server 2008, so be sure to read the Help file for whichever version you're using.

As the name implies, the Invoke-SQLCmd cmdlet's job is to run a SQL command. It does so through its Query parameter. Essentially, any T-SQL expression can be executed from the PowerShell prompt, like this:

PS C:> invoke-sqlcmd -query "select @@version"

Figure 11 shows sample results.

Figure 11: Retrieving the SQL Server Version

Because I was on the SQL Server server and ran the command for the local SQL Server instance, it defaulted to the local host. But you can specify a different machine and instance, like this:

PS C:Usersadministrator> invoke-sqlcmd "select @@version"  -ServerInstance "chi-win7-22"

When you have the SQL Server PowerShell bits installed on your desktop, you can manage multiple servers from one location, which is handy. But for now, let's run a few commands on the local server. Suppose you have a script, New-ComputerDataDB.sql, that creates a database named ComputerData. You can use the Invoke-SQLCmd cmdlet to run that script:

PS C:> invoke-sqlcmd  -InputFile C:scriptsNew-ComputerDataDB.sql

Besides creating a database, you can create a new table from the PowerShell prompt. To do so, you need to create a here-string that contains the query. A here-string is a multi-line string created using a special technique. Here's an example that creates a table named Computers:

PS C:> $query=@"CREATE TABLE dbo.Computers(  Computername ntext NOT NULL,  ReportDate datetime NULL,  PhysicalMemoryBytes bigint NULL,  OS ntext NULL,  ServicePack int NULL,  RegisteredOwner ntext NULL,  RegisteredOrganization ntext NULL)"@

Note that if you're creating the here-string in the PowerShell ISE, you need to make sure everything is left-justified. After the command runs, $query will contain the code to create the Computers table. You can then run that code with a command such as:

PS C:> Invoke-Sqlcmd -query $query  -ServerInstance CHI-DB01  -Database ComputerData

This command creates the Computers table in the ComputerData database located on the chi-db01 instance.

You can do a lot more than just create tables and databases. For example, I wrote a simple function, Update-MyDB, to update the Computers table with information from my network. I loaded this function into my PowerShell session using Invoke-SQLCmd:

Function Update-MyDB {Param([System.Management.ManagementBaseObject]$os)invoke-sqlcmd -query "INSERT INTO Computers  (Computername,ReportDate,PhysicalMemoryBytes,OS,  ServicePack,RegisteredOwner,RegisteredOrganization)  Values ('$($os.csname)','$(get-date)','  $(($os.TotalVisibleMemorySize * 1kb))','  $($os.caption)','$($os.ServicePackMajorVersion)','  $($os.RegisteredUser)','$($os.Organization)' )"  -serverinstance "CHI-DB01" -database ComputerData} #end function

(Note that you can download this code by clicking the Download the Code button.) Once the function was loaded, I was able to run a code snippet like this:

$computers = Get-Content "MyComputers.txt "foreach ($computer in $computers) {  Try {    # Get WMI information.    $wmi=Get-WmiObject win32_operatingsystem `  -computer $computer -ErrorAction Stop    Write-Host "Inserting $($wmi.CSName)" `  -ForegroundColor Green    # Update the SQL Server table.    update-mydb $wmi  }  Catch {    Write-Warning ("{0}: {1}" `  -f $computer,$($_.Exception.Message))  }} #foreach

This code snippet first retrieves the list of computers in MyComputers.txt. Then, for each computer on the list, it retrieves information about the OS using Windows Management Instrumentation (WMI). Finally, it runs the Update-MyDB function to update the information in the Computers table. For me, this is the best part of managing SQL Server from PowerShell because I can integrate SQL into my PowerShell commands. It's also a snap to query the data right from PowerShell, like this:

PS C:> Invoke-Sqlcmd -Query "Select * from Computers"  -ServerInstance CHI-DB01 -Database ComputerData

You can see the results in Figure 12.

Figure 12: Retrieving Computer Information from the ComputerData Database

You'll likely use Invoke-SQLCmd for the majority of your work in PowerShell.

Backing Up and Restoring Databases

The last examples I'll show you are how to back up and restore a database. You can check when the last full database backups occurred using the SQLServer provider:

PS C:> dir sqlserver:sqlchi-db01defaultdatabases |  select Name,LastBackupDate

As you can see in the sample results in Figure 13, the databases are listed with the date and time of their last full backup.

Figure 13: Listing When the Last Full Database Backups Occurred

Databases with a backup date of 1/1/0001 have never been backed up. To perform a backup, you can use the Backup-SQLDatabase cmdlet. Here's how to back up the ComputerData database to a file:

PS C:> backup-sqldatabase -ServerInstance chi-db01  -Database ComputerData  -BackupFile "E:BackupComputerData_01.bak"

If you want to use the default backup settings, you can back up all the databases with an incredibly easy command:

PS C:> dir sqlserver:sqlchi-db01Defaultdatabases |  Backup-SqlDatabase

The first part of the command uses the SQLServer provider to list the databases, which are then piped to the Backup-SQLDatabase cmdlet, which performs the backups.

Restoring a database from a backup is equally easy, thanks to the Restore-SQLDatabase cmdlet. For a simple database, you can restore it like this:

PS C:> Restore-SqlDatabase  -ServerInstance CHI-DB01 -Database ComputerData  -BackupFile E:backupComputerData_01.bak  -RestoreAction Database -ReplaceDatabase

Restoring a database is a delicate and potentially complex operation, so please read the Restore-SQLDatabase cmdlet's Help file and examples. In addition, try all of these commands with test data before using them in production.

The Best Way to Learn PowerShell

PowerShell offers some great opportunities for SQL Server pros, especially with SQL Server 2012. I hope that I have piqued your interest and offered enough information to get you started. If you're looking for more help, I encourage you to use the forums at PowerShell.org. If you're totally new to PowerShell, you might want to pick up a copy of Learn Windows PowerShell 3 in a Month of Lunches (Manning Publications, 2012). Or if you need a more comprehensive resource, grab PowerShell in Depth (Manning Publications, 2013). But honestly, the best way to learn PowerShell is to begin using it every day.