Microsoft’s PowerShell and SQL

Introduction to PowerShell with SQL


Don’t Do It!

My advice for beginners is: ‘Avoid PowerShell and SQL!’  My thinking is use SQL’s own tools to extract and present the data.  PowerShell is capable of performing SQL management tasks such as backup, but even here I believe that SQL’s native commands are better.

I recommend that PowerShell beginners start with scripts that interrogate the operating system.  Understanding what PowerShell has to offer SQL is really only for database specialists, or for those who just like to push the boundaries and discover what PowerShell can achieve.

A Reminder of PowerShell’s Strengths

PowerShell is great for managing any modern Windows operating system starting with commands such as Get-Process, Restart-Service, Get-Eventlog -Logname system.  Then there are excellent PowerShell modules for administering Active Directory.  As for Exchange, some would say managing Exchange Server is the killer reason to learn PowerShell. 

Yet moving on to consider SQL and PowerShell, I find there is no synergy here, it’s just a case of extending what PowerShell can do with the aforementioned operating systems or AD.  The crux of the matter is SQL is just not as friendly to PowerShell as Exchange, and there are not many specialist cmdlets.

How PowerShell Interacts with SQLSQL in PowerShell

The key to using PowerShell commands on SQL Server is the provider sqlps.exe.  It also loads and registers the SQL Server snap-Ins.

Launching PowerShell is easy enough, just right-click a database and select ‘Start PowerShell’.

Trap: SQL 2008 comes with PowerShell v 1.0, and once SQL is installed you cannot upgrade to PowerShell 2.0 or later!

Workaround: Install PowerShell 2.0 BEFORE you install SQL.

Just to give perspective, check which providers are installed, and which ‘Drives’ are available.

# Check PowerShell Providers

Amongst the results is the FileSystem provider, this is a reminder that once you have installed splps.exe then you can ‘walk’ the SQL server and its databases, just as you would the C:\. … Windows  … \System 32

Get-ChildItem -Path SQLServer

One minor irritation is the way PowerShell and SQL interpret the humble \.  You have to encode it as %5,.  This is another example of SQL not being friendly to PowerShell.  It’s not that this is hard, but I keep thinking ‘SQL’ manager does this better, something I never felt when working with PowerShell and Exchange.

Server\CompanyAcc  # As seen in SQL Manager
# In PowerShell would be:

Guy Recommends:  A Free Trial of the Network Performance Monitor (NPM)Review of Orion NPM v11.5 v11.5

SolarWinds’ Network Performance Monitor will help you discover what’s happening on your network.  This utility will also guide you through troubleshooting; the dashboard will indicate whether the root cause is a broken link, faulty equipment or resource overload.

What I like best is the way NPM suggests solutions to network problems.  Its also has the ability to monitor the health of individual VMware virtual machines.  If you are interested in troubleshooting, and creating network maps, then I recommend that you try NPM now.

Download a free trial of Solarwinds’ Network Performance Monitor

Sample PowerShell Cmdlets for SQL

SQLServer: (Provider)
Backup-SQLDatabase  (Probably the most useful SQL cmdlet)
Remove-Item (Works!)
New-Item (Does not work with SQL – Unfriendly)
Decode-SQLName (Not very welcoming)
Encode-SQLName (Exchange does not need encode / decode)

Note: In common with other PowerShell scripts by default sqlps runs with the scripting execution policy set to Restricted.

See more PowerShell real-life tasks »

Summary of PowerShell and SQL

There are more rewarding places for a PowerShell to start their career than SQL.  I could tolerate SQL’s quirks if PowerShell could manage the databases as easily as it manages Exchange Servers.

To my thinking managing SQL with PowerShell is just for the sake of it, SQL’s own tools are faster, easier and more comprehensive for both database mining and for server management.

If you like this page then please share it with your friends


See more Microsoft PowerShell Examples of Real Life Tasks

PowerShell Real-life Examples   • Test-Connection   • Invoke-Expression   • Invoke-Command

Com   • Shell Application   • Measure-Object   • PowerShell Registry   • Compare-Object Registry

PowerShell and Exchange   • PowerShell and SQL   • Restore-Computer   • Engineers Toolset

Please email me if you have a better example script. Also please report any factual mistakes, grammatical errors or broken links, I will be happy to correct the fault.