SQL PowerShell Demo - Intro

  • Article

SQL PowerShell Demo - Intro

Cindy Gross, Dedicated Support Engineer  

Audience: SQL Server DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

0. Prerequisites

1. Install Basics

  • Check what version of PowerShell you currently have on your desktop.
    • Run PowerShell.exe with the “run as administrator” option
    • Get the version by executing the following in the PowerShell window:

# Get the version

$host.version

2. Hints

  • To paste into a PowerShell window (or a dos-prompt), copy your source then right click in the PowerShell window to paste. If you paste something that has a bullet point you will need to delete the o: or hidden character/space at the beginning before you hit enter.
  • To copy from a PowerShell window (or a dos-prompt), right click in the menu bar at the top. Choose edit.mark and highlight a “box” of data. You’re basically drawing from one corner to another, not picking individual words. Then without clicking anywhere else within the window, right click on the menu bar again and choose edit.copy. Then paste wherever you need to.
  • The pound sign (#) indicates a comment. You can cut/paste the demo steps including the lines with #. However, be careful about pasting an entire section at once, it will execute them all and the results may scroll off the screen.
  • For my SQL Server connections in this demo, I have used localhost to indicate the default instance on my local box. If you want to connect to a named instance, to an instance on another box, or to any clustered instance, replace localhost with the instance name (i.e. SERENITYHOME\Instance 1 or SomeRemoteSQL or localhost\Instance1). Note that localhost is equivalent to a period (.) or (local).
  • Within SQLPS even a default instance needs an “instance” name specified and for a default instance the name is DEFAULT. For example this is the default instance on my local box: SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables\Purchasing.Vendor
  • To change the dimensions of the PowerShell window (current and future), right click in the menu bar and choose properties. Here you can change the height, width, font, etc.
  • Many basic dos commands still work, such as cls to clear the screen.

3. Explore Basic PowerShell Functionality

  • Run PowerShell.exe from the start menu with the “run as administrator” option. PowerShell should be in your path, but if you need to find the source location, look in a path similar to C:\Windows\System32\WindowsPowerShell\v1.0.
  • Check your security level. I have put the # sign (comment) on the lines that are not PowerShell commands so that you can cut/paste the entire section into PowerShell or a script. See the hints section on how to cut/paste.

# Run this command to see what your security level is:

Get-ExecutionPolicy

# For information about execution policies:

get-help about_execution_policies

# If the execution policy is restricted then change it to unrestricted, RemoteSigned, or whatever level

# is most appropriate https://technet.microsoft.com/en-us/library/ee176961.aspx

# Example of changing the execution policy:  

Set-ExecutionPolicy unrestricted

  • In the PowerShell window run these commands and review the output. This is just a small sampling of the information available via Windows cmdlets.

# List of available commands and info about each:

get-help

# “Dir” is an alias for get-childitem so we see the help info for get-children when we pass in dir:

get-help dir

# “Ls” is also an alias for get-childitem:

get-help ls

# The “full” option includes examples, parameters, etc.:

get-help dir –full

# Returns one row per running instance of sqlservr.exe:

get-process sqlservr

# Returns one row per running instance of sqlservr.exe with different output:

get-process sqlservr -fileversioninfo

# one row per service with SQL in the name:

get-service *sql*

# system path from environment variables/set:

$env:path

# All environment variables that start with proc:

Dir env:proc*

# List of all PowerShell “drives” including basic regkey hives:

Get-psdrive

# Look at a registry key, in this case we can see which SQL instances are installed:

# Note that you can leave out HKEY_LOCAL_MACHINE since that is covered by the switch to hklm

# It appears that the HKEY_LOCAL_MACHINE part of the name is case sensitive

#32bit instances on an x64 box have keys in the Wow6432Node key

CD hklm:
dir “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names”

dir “HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names”

4. Formatting

# Look at some of the output format options

# Switch to the registry

CD hklm:

dir “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names” | format-list

dir “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names” | format-list –property name, property

dir “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names” | format-list –property *

5. Run a PowerShell Script

# Change to the directory where the PowerShell script resides

cd c:\installs\PowerShell\test

# Specify the current directory with a period (.) and call the PowerShell script

.\getsysproperties.ps1

  • You can also run the PowerShell script from a dos-prompt
    • From start, execute cmd with “execute as administrator”. # is not a valid comment indicator in dos, so use REM instead.

REM Execute the script

cd c:\installs\PowerShell\test

PowerShell.exe -noexit .\getsysproperties.ps1

  • (Optional) Save one or more of the earlier PowerShell cmdlet calls in a .ps1 file and test executing them.
  • Close your PowerShell window and CMD window.

6. Run a SQL PowerShell cmdlet

  • On a box with the SQL 2008 client tools installed, run sqlps.exe with the “run as administrator” option which opens a PowerShell mini-shell window (it will look like a normal dos-prompt but have “PS SQLSERVER:\>” at the prompt). Run these commands and explore the output (where you see localhost you can enter your own SQL Server server\instance name)

# Look at the syntax and options for the cmdlet that lets you execute any SQL query

Get-help invoke-sqlcmd

# Connect to SQL and return the version information

invoke-sqlcmd -ServerInstance localhost -Query "select @@version"

# The output was cut off, let’s try a different format

invoke-sqlcmd -ServerInstance localhost -Query "select @@version" | format-list

# Close the sqlps window

  • From SSMS 2008+, right click on any SQL Server instance name and choose “start PowerShell”. You will see a dos-prompt open with something like PS SQLSERVER:\SQL\SERENITYHOME\DEFAULT (my instance =is a default instance on the server SERENITYHOME). That line shows the context, I no longer have to specify the instance name for invoke-sqlcmd. I do still have to specify a database if I want a context other than my default database.

# No instance name is specified, and it puts me in the default database for my Windows auth login

invoke-sqlcmd -Query "select @@version, db_name()"

# I can specify a specific database as a parameter

invoke-sqlcmd -Query "select @@version, db_name()" –database tempdb

# Close the “SQL Server PowerShell” window

  • From SSMS 2008+, right click on any SQL database or SQL database object and choose “start PowerShell”. When I do this for AdventureWorks2008.HumanResources.Department I see a dos-prompt open with PS SQLSERVER:\SQL\SERENITYHOME\DEFAULT\Databases\AdventureWorks2008\Tables\HumanResources.Department>). That’s the context, I no longer have to specify the instance name or database name for invoke-sqlcmd. I can then change to another database and/or another SQL Server (in this case I switched to the Kaylee instance and the AdventureWorks database).

# The database context is the database I clicked on in SSMS

invoke-sqlcmd -Query "select @@servername, db_name()"

# We can change to another db (if you do not have AdventureWorks, choose another database)

cd SQLSERVER:\sql\serenityhome\kaylee\databases\AdventureWorks

# Now look at the current database context

 invoke-sqlcmd "select @@servername, db_name()"

# Close the “SQL Server PowerShell” window

7. Execute from a SQL Agent Job

  • In SSMS, go to SQL Server Agent.Jobs and right click to create a new job called Test PS.
  • On the Steps page create a new job step and set the “type” = “PowerShell”.
  • I used “Run as” = “SQL Server Agent Service Account”, make sure whatever account you use has sufficient permissions.
  • Command = invoke-sqlcmd -serverinstance localhost -database TempDB -query "select getdate() AS RunTime, @@servername AS ServerInstance, db_name() AS DBName" > c:\temp\pstest.txt
  • Note that if you don’t specify the ServerInstance parameter it will try to connect to the local default instance even if you run from a job on a named instance. The above command will connect to the local default instance, change it to another instance if needed.
  • Save and run the job.
  • Run notepad c:\temp\pstest.txt and see that it outputs the expected ServerInstance and DBName.
  • Note that each job step opens its own sqlps.exe which takes about 20-40MB of memory.

8. SQL from Windows PowerShell

  • Close any existing PowerShell windows.
  • Run PowerShell.exe.
  • Try to switch to the SQL mini-shell and it will fail

# Change to the SQL mini-shell

Cd sqlserver:\

  • In PowerShell, run sqlps to load the SQL provider and cmdlets.

# Load SQLPS

sqlps

  • Try to switch to the SQL mini-shell and it now works

# Change to the SQL mini-shell

Cd sqlserver:\

  • You can now do things like get-help invoke-sqlcmd because loading sqlps loaded the SQL cmdlets.

# Test a SQL cmdlet

invoke-sqlcmd -ServerInstance localhost -Query "select @@version"

  • BOL topic “Running SQL Server PowerShell” has other ways to load the SQL provider/snap-ins and SMO to Windows PowerShell.

9. Run a SQL PowerShell Script

Open notepad and create c:\temp\TestSQL.ps1 (make sure .txt is not added to the end). Paste in this text:

# get a list of instances on this server

cd hklm:

dir "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\"

# see what we can admin

cd sqlserver:\

ls

# see what the machine name is

cd \sql

ls

# issue a query

invoke-sqlcmd -ServerInstance localhost -query "SELECT top 5 name FROM sys.databases ORDER BY name"

  • In Windows PowerShell load the SQL cmdlets and then execute the SQL PowerShell script

# Load the SQL cmdlets

Sqlps

# Change to the C:\temp folder

Cd c:\temp

# Execute the PowerShell script

.\testsql.ps1

  • By default the format is slightly different for some of the output when executed from a script.

10. Cleanup

  • Close the PowerShell window.
  • Delete c:\temp\testsql.ps1 and c:\temp\pstest.txt
  • Delete the SQL Agent job you created.

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

PowerShell for SQL Server - Basics https://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx