PowerShell- SQLps

Published 03 May 11 07:42 PM | MartinCroft

Day 3, of a blog a day I was hoping for more time for this post but we’ll see how it goes. I looked through some of my Google Documents and found some interesting bits that I’d collected around PowerShell so i thought I’d put them in a blog for future reference and have a play around with it.

and as it this SQL related I’ll stick to SQLps. It can be evoked from a command prompt or for those not of an older generation with the run command. SQLPS runs a slimmed down PowerShell session with the SQL Server PowerShell provider and cmdlets (more on these later)loaded and registered.

sqlps

Console window after running SQLps and doing a Dir.

PowerShell can be used to connect to SQL2008, SQL 2005 from SP2 and SQL 2000 from SP4

SQL Server 2008 version of SQL Server Management Studio supports starting PowerShell sessions from the Object Explorer tree,

sqlps-mangementstudio

Right click database / table or at any level in object explorer and select PowerShell.  In the shot above this open’s a cmd line Powershell application within that database tables, and the ability to change to directory's for a variety of objects

Powershell scripts can also be executed through SQL Agent in SQL 2008.

CmdLets

CmdLets are single function command, that can be quite powerful, however security is locked down out of the box and ironically to unlock it uses a CmdLet

Security

The OS wont run PowerShell scripts by default. Therefore we need to adjust the ExecutionPolicy to allow Powershell scripts to run. you set the ExecutionPolicy through a cmdlet .To get the current Execution policy run

get-ExecutionPolicy

running the following will give help on the cmdlet

set-ExecutionPolicy –?

Short cut keys

It might be a good point to mention a few shortcut keys , if you typed in

set-ex

and pressed tab, it would auto complete the word. If there was multiple possible combinations you could cycle through all possible options.

F7 – Will show the history of last 50 commands in a popup window and you can cursor through the command ran.

F8 - More functionality that arrow up, can search for specific command already ran e.g type in d(F8) and brings up command starting d, to get the next d command press F8 again.

F9 – Allows entry of a command number

There are multiple execution policies , the default being Restricted, which permits individual commands but wont run scripts.  More details can be found here set-ExecutionPolicy –?

setting the execution level to remote signed will allow script to be execute.

set-ExecutionPolicy  RemoteSigned

you might come across the following issue if running Vista / Windows 7

sqlps-accessdenied

If this is the case, run SQLps as administrator, start run type SQLps and when it comes up right click and Run As Administrator,  once logged in run the set-executionpolicy and when opening back up next time it should be set.

This was a brief forage into Powershell I’ll look to go more indepth in some future posts, I do have 31 days in May. This task is looking slightly ugly, like the looks the wife has been giving me.

Filed under: ,

Comments

No Comments