Cindy Gross: Small Bites of Big Data, Small Data, All Data

Small Bites of Big Data, Small Data, All Data for Hadoop, SQL Server, Hive, Distributed Systems, Scale Out....

PowerShell for SQL Server - Basics

PowerShell for SQL Server - Basics

Rate This
  • Comments 2

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

  • 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: www.microsoft.com/.../search.aspx

Page 1 of 1 (2 items)
Leave a Comment
  • Please add 6 and 8 and type the answer here:
  • Post