Powershell Day 16

Day 16 Introduction to Mini Shell

Mini Shell or SqlPS is the default shell utility provided by Microsoft to support the sql server. This shell is default comes with Powershell i.e. It will be default from sql server 2008 onwards.  And it is supported by Microsoft, means if you have any issue on your mini shell you can call PSS and they will help you out. Due to all this. It has limited task to do with sql server.

The SQL Server PowerShell (SQLPS) snap-ins are as follows:

  • Microsoft.SqlServer.Management.PSProvider.dll

Implements the SQL Server PowerShell provider and associated cmdlets such as Encode-SqlName and Decode-SqlName.

  • Microsoft.SqlServer.Management.PSSnapin.dll

Implements the Invoke-Sqlcmd cmdlet.

This two snapins will in. we cannot register any external snapins (from any custom or third party spanins ) allowed in sqlps. Because external spanins can be changes by anyone at anytime and due to which system may be unstable as sqlps is fully supported by Microsoft so any Microsoft doesnot support any extended feature added by any thirdparty snapins.

Sqlps is most Good for invoke-sqlcmd, which is nothing but running command prompt sqlcmd.

Different ways to open sqlps/mini shell/ sql server Powershell.

  1. SQLPS
    1. As we run Powershell by just

C:\>Powershell

Or

Start->run ->Powershell

    1. In the same way

SQLPS can be run from command prompt

C:\>SQLPS

 Or

Start->run->sqlps

Like for windows Powershell the shell will be like

PS C:\>

PS : Powershell and the C: is current directory

For sqlps

PS SQLSERVER:\>

SQLSERVERVER : is the sql drive. As we discussed in our ealier blog post when we were discussing about “Registery, Variable and Alias. That when we run PSDrive Cmdlets it will show all the drives exist on the system. And Powershell takes some components as drive. In the same way- Powershell treat “Sqlserver” also as a drive .

When we run Sql powersehll (mini shell/SQLPS). And run psdrive

Eg. PSDRive.

Means its like a drive means all the drive commands/cmdlets can be used on it.

Lets start with dir/get-childitem/ls

Ohhh, great, here you can cd to “Sql”/ policy/ data collection. Cool.

SQL : directory is having all the sql server related information, it will show you all the sql instance installed on your computer.

So when you reach to the server, you will get all the objects we can go into it. Like “databases” will be same as databases in our management studio and so on…

  1. Management studio:

Using management studio you can go to sql Powershell, this is supported from sql version 2008 onwards where sql is build in. just open management studio and go to any tree object of sql server like database/table/views…. And right click, select the “Start Powershell” that’s it. Powershell will move to the respected folder where we pointed out.

Eg. If we go to “database” and rightclick and start Powershell will go to “PS SQLSERVER:\sql\L-108326-b\sqlserver2008se\databases>” directory.

Means using Powershell we can work with sql server just like we use directory, nice. And very fast. as management studio may take some resources. That way performance improvement.

Sql Powershell has introduced some more cmdlets in addition to the windows cmdlets

Sqlserver CmdLets

  • Invoke-Sqlcmd
  • Invoke-PolicyEvaluation
  • Encode-SqlName
  • Decode-SqlName
  • Convert-UrnToPath

Invoke-sqlcmd : will invoke the sqlcmd for Powershell to run any query on the database.

Eg.

PS: SQLSERVER\…\databases:\>Invoke-sqlcmd –Query “select * from master.sys.databases”

Will show you list of all the databases on the

or

PS: SQLSERVER\…\databases\master:\>Invoke-sqlcmd –Query “select * from \sys.databases”

Generally when you are at “databases” folder and say “dir” you will get only user databases.

To get system databases as well in you dir cmdlets use switch “-force”

PS: SQLSERVER\…\databases:\>dir –force

About these ads
This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s