Cindy Gross, Dedicated Support Engineer

 Audience: SQL DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

Basics

• PowerShell processes objects based on .NET.
• Cmdlets are named in a Verb-Noun format such as Start-Service and Get-Help.
• For help on any Cmdlet, use: get-help <cmdlet-name> -detailed.
• Scripting is good for things like automating system administration tasks. Scripting languages include VBScript, CScript, and PowerShell.
• Basic script editing can be done in notepad.exe.
• SQL08+ installs sqlps.exe which is SQL’s PowerShell interface.
• If you run sqlps.exe on its own it is a “mini-shell” with all the SQL functionality but not all of the Windows functionality.
• If you open Windows PowerShell (PowerShell.exe) and then run sqlps to load the SQL provider and cmdlets you have both SQL and Windows functionality.
• For the most part scripting is NOT case sensitive (though I found one registry key that is case sensitive – HKLM).
• PowerShell scripts have the .ps1 suffix and you always specify the full path (or .\ for the current dir) when you call the script.

SQL

• You must use the SQL08+ components to use PowerShell, but you can connect to SQL 2005 SP2+ and SQL 2000 SP4+ though the functionality may be limited.
• Sqlps.exe and SSMS (right click, start PowerShell) start PowerShell with the SQL Server PowerShell provider and cmdlets loaded.
• Within Windows PowerShell you can run sqlps to load the SQL cmdlets into Windows PowerShell.
• The main cmdlet for SQL is Invoke-SQLCmd which lets you run any SQL query. The other cmdlets in SQL Server 2008/2008R2 are invoke-PolicyEvaluation (PBM), Encode-SQLName/Decode-SQLName (format SQL identifiers), and Convert-UrnToPath (navigation). You can also load the SQL Server Management Object (SMO) DLLs for additional functionality.
• Read gettingStarted.rtf in the “Windows PowerShell 1.0 Documentation Pack” and complete the demo steps.

Learning

• Go through “Scripting: Your First Steps” http://technet.microsoft.com/en-us/scriptcenter/dd940112.aspx
• Follow the demo steps in my SQL PowerShell Demo blog.
• Take a look at the Databases.SQL Server scripts here: http://gallery.technet.microsoft.com/ScriptCenter/en-us

Syntax

• Compare with –eq –ne –gt –like
• $_ is the current object
• The main difference between the script hosts cscript and wscript (the default) is that echo for cscript is to the command window and echo for wscript is to a message box.
• If a path has a space you need to add & “…”. For example: & “C:\temp\my PowerShell\mytest.ps1”.
• The current path is . as in .\mytest.ps1

References

SQL Powershell

• SQL Server PowerShell Overview http://msdn.microsoft.com/en-us/library/cc281954.aspx
• Running SQL Server PowerShell http://msdn.microsoft.com/en-us/library/cc281962.aspx
• Understanding and Using PowerShell Support in SQL Server 2008 http://sqlblog.com/blogs/allen_white/archive/2009/06/13/powershell-whitepaper-published.aspx
• Allen White’s Blog http://sqlblog.com/blogs/allen_white/default.aspx
• Buck Woody’s Blog http://blogs.msdn.com/b/buckwoody/archive/tags/powershell/
• Enterprise Policy Management Framework to automate SQL Policy Based Management with PowerShell http://epmframework.codeplex.com/

Windows Powershell

• Technet Script Center http://technet.microsoft.com/en-us/scriptcenter/default.aspx
• What Can I Do With Windows PowerShell? http://technet.microsoft.com/en-us/library/ee332526.aspx
• Windows PowerShell Blog http://blogs.msdn.com/b/powershell/
• Sesame Script / Learn Beginning Scripting http://technet.microsoft.com/en-us/scriptcenter/dd772284.aspx
• Richard Siddaway’s Blog – Of PowerShell and Other Things http://richardsiddaway.spaces.live.com/default.aspx
• Hey, Scripting Guy Blog  http://blogs.technet.com/b/heyscriptingguy/
• Using Format Commands to Change Output View http://technet.microsoft.com/en-us/library/dd347677.aspx
• Chad Miller's Blog – Sev17
• Mike Shepard's Blog – PowerShell Station
• Laerte Junior's Blog - $hell Your Experience
• Steven Murawski's Blog – Real Admins Script
• Bernd Kriszio's Blog - PauerSchell
• Max Trinidad's Blog – The PowerShell Front

Downloads

• PowerShell Forums http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.windows.powershell
• Windows PowerShell Quick Reference http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=df8ed469-9007-401c-85e7-46649a32d0e0
• Windows PowerShell 1.0 Documentation Pack http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b4720b00-9a66-430f-bd56-ec48bfca154f
• Windows PowerShell Graphical Help File http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=3b3f7ce4-43ea-4a21-90cc-966a7fc6c6e8
• PowerGui http://www.powergui.org/index.jspa

My other PowerShell blogs:

Powershell for SQL - Getting Started http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-getting-started.aspx

SQL PowerShell Demo - Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx