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.
- 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.
- 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.
- 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
- 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:tempmy PowerShellmytest.ps1”.
- The current path is . as in .mytest.ps1
- 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/
- 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
- 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
2 thoughts on “PowerShell for SQL Server – Basics”
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 )
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: http://www.microsoft.com/…/search.aspx