PowerShell for SQL Server - Basics

PowerShell for SQL Server - Basics

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

Windows Powershell

• Technet Script Center https://technet.microsoft.com/en-us/scriptcenter/default.aspx
• What Can I Do With Windows PowerShell? https://technet.microsoft.com/en-us/library/ee332526.aspx
• Windows PowerShell Blog https://blogs.msdn.com/b/powershell/
• Sesame Script / Learn Beginning Scripting https://technet.microsoft.com/en-us/scriptcenter/dd772284.aspx
• Richard Siddaway’s Blog – Of PowerShell and Other Things https://richardsiddaway.spaces.live.com/default.aspx
• Hey, Scripting Guy Blog  https://blogs.technet.com/b/heyscriptingguy/
• Using Format Commands to Change Output View https://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 https://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.windows.powershell
• Windows PowerShell Quick Reference https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=df8ed469-9007-401c-85e7-46649a32d0e0
• Windows PowerShell 1.0 Documentation Pack https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b4720b00-9a66-430f-bd56-ec48bfca154f
• Windows PowerShell Graphical Help File https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=3b3f7ce4-43ea-4a21-90cc-966a7fc6c6e8
• PowerGui https://www.powergui.org/index.jspa

My other PowerShell blogs:

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

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

Comments

• Anonymous
  November 17, 2011
  I am using PowerShell 2.0 since the release of SQL Server 2008 R2 for my SQL Server 2008 and 2008 R2 on a XP Pro SP3 and a Windows 7 Home Premium . But why the documentation is never warning that there are 2 versions pour PowerShell ? Why it is so complicated to find the V2..0 to download. These 2 versions are as different as chalk and cheese, a real pity...I hope you will excuse my poor english ( and my old french -> English dictionary )Please, could you update your article to give some links about the V2.0 version of Powershell  ? ( it will be necessary for SQL Server 2012 if i have well understood its documentation )
• Anonymous
  November 19, 2011
  The link to download PowerShell 2.0 is in my PowerShell Demo blog blogs.msdn.com/.../sql-powershell-demo-intro.aspx - support.microsoft.com/.../968929. You can also find the download location here: www.microsoft.com/.../search.aspx