Introduction to PowerShell with SQL
- Don’t Do It!
- A Reminder of PowerShell’s Strengths
- How PowerShell Interacts with SQL
- Sample PowerShell Cmdlets for 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 SQL
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
Clear-Host
Get-PSProvider
Get-PSDrive
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:
Server%5CompanyAcc
Guy Recommends: A Free Trial of the Network Performance Monitor (NPM)
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)
Restore-SQLDatabase
Invoke-PolicyEvaluation
Invoke-SqlCmd
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.
