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.


  • 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.



  • 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


SQL Powershell

Windows Powershell


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

Comments (2)

  1. Papy Normand says:

    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 )

  2. CindyGross says:

    The link to download PowerShell 2.0 is in my PowerShell Demo blog blogs.msdn.com/…/sql-powershell-demo-intro.aspxsupport.microsoft.com/…/968929. You can also find the download location here: http://www.microsoft.com/…/search.aspx