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:
Implements the SQL Server PowerShell provider and associated cmdlets such as Encode-SqlName and Decode-SqlName.
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.
- As we run Powershell by just
- In the same way
SQLPS can be run from command prompt
Like for windows Powershell the shell will be like
PS : Powershell and the C: is current directory
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
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…
- 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
Invoke-sqlcmd : will invoke the sqlcmd for Powershell to run any query on the database.
PS: SQLSERVER\…\databases:\>Invoke-sqlcmd –Query “select * from master.sys.databases”
Will show you list of all the databases on the
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