SQL Server 2008 – PowerShell in SQL Server

SQL Server 2008 introduces support for Microsoft PowerShell. PowerShell is a new command-line shell and scripting language which offers more functionality than Windows command prompt utilities.


Read more about powershell here http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

I was planning to blog about this for long time but the problem is, PS is an ocean in itself. For sure, very powerful and very useful in day to day DBA activities. When I started exploring this utility I could figure out many scenarios where SQLPS can really help. Like , backup file management(or for that matter any file management) like moving/deleting old backup files. With my little knowledge and experience in SQL Server I am sure we can write many utility tools using SQLPS.

How to go to SQL Powershell (PS) prompt

(a) Start - - ->> Run -- ->> Cmd -- ->> sqlps
(b) I got a prompt like “PS C:\Documents and Settings\Madhu>
(c) Now I want to change the location to my SQL Server instance and databases

PS C:\Documents and Settings\Madhu>Set-Location SQL:\LHIL075\SQL2008FEB\Databases

OR

PS C:\Documents and Settings\Madhu>CD SQL:\LHIL075\SQL2008FEB\Databases

Note : CD is an aliases for Set-Location cmdlet.

(d) I get the following prompt
PS SQL:\LHIL075\SQL2008FEB\Databases>
(e) Now you can do Get-ChildItem or DIR to get the object in this node


There are many useful cmdlets available. In the coming months we should find many useful utilities using PS.

Using Invoke-Sqlcmd (one of the cmdlet available)
The Invoke-Sqlcmd cmdlet lets you run your sqlcmd script files in a PowerShell environment. Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.
This is an example of calling Invoke-Sqlcmd to execute a simple query, similar to specifying sqlcmd with the -Q and -S options:

Summary
Simple... need to explore more. I will be updating with few sample scripts soon