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

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

Windows Powershell

Downloads

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