SQL DBA learning curve

I was looking for some ways to connect to connect to SQL instance .. Sarabpreet had covered quite interesting ways to do the same … you may check his article over here (a big Thanks to Sarab for sharing the same …)

below is what I tried on windows 7 (SQL 2008 R2 )

once we are within shell .. shell treats all objects with SQL

as files and folders … like what we have while we are working with CMD

we can use different ways to connect to sql server via PowerShell tools ..

1> type powershell.exe   in the start –> run prompt and hit enter

this will keep you PowerShell prompt

2> type SQLPS.EXE   in the start –> run prompt and hit enter

3> or right click on any tab in the SSMS and click on Start PowerShell

depending on were you have right clicked .. you be taken to appropriate

depth or hierarchy with in the SQL instance

for this example we will use step 3 …

refer the snapshot on the right …

if you right click on any of the object within the SQL instance .. we will

taken into the corresponding hierarchy level within the SQL

once we are within the database .. we will use command DIR .. to look for

list of folder and files within (comparing the database structure to OS file system )

Note :: after a certain level command will not return any results or output …

below is an example of a command ran at database level .. we can all objects that are available in SSMS for  a database

we will need to use a SQLPS command named INVOKE-SQLCMD to get any meaning full data out of the SQL server ..

once OS based commands like dir (which will work same as LS and get-childitem) hits its limits ..

1> get-help invoke-sqlcmd -FULL

2> get-help invoke-sqlcmd -EXAMPLE

3> get-help invoke-sqlcmd

invoke-SQLCMD has multiple parameters which help use to define query and connection options

so once we are within correct location (use cd or cd .. to move from one path to another .. )

we will select data from a table ..as below  .. command used is as below (we may try it on any table with some data in it)

invoke-sqlcmd “select top 10 * from dbo.T2 where id > 1000”

below is an example of using SQLCMD to run another command to populate data ..

so you can run any t-sql command in SQLPS shell.. that use can run in SSMS

so what is use of all this .. if we can run these commands in SSMS … well the power of powershell

really comes across when we have list of servers to manange .. and we want to automate some admin

tasks across all of them …. going forward I will share more examples on Powershell …

Enjoy:)