In SQL Server 2012, the original mini-shell has been deprecated, and SQLPS is now provided as a module. Launching PowerShell from SSMS now launches a Windows PowerShell session, imports the SQLPS module, and sets the current context to the item the PowerShell session was launched from. DBAs and developers can then start navigating the object hierarchy from here.
In this recipe, we will navigate the SQL Server PowerShell hierarchy by launching a PowerShell session from SQL Server Management Studio:
Right-click on your instance node.
Click on Start PowerShell. This will launch a PowerShell session and load the
SQLPSmodule. This window looks similar to a command prompt, with a prompt set to the SQL Server object you launched this window from:
Note the starting path in this window.
Type
dir. This should give you a list of all objects directly accessible from the current server instance—in our case, from the default SQL Server instance KERRIGAN. Note that dir is an alias for the cmdletGet-ChildItem.
This is similar to the objects you can find under the instance node in Object Explorer in SQL Server Management Studio.
While our PowerShell window is open, let's explore the SQL Server
PSDrive, or the SQL Server data store, which PowerShell treats as a series of items. Typecd\.This will change the path to the root of the current drive, which is our SQL ServerPSDrive.Type
dir. This will list all Items accessible from the root SQL ServerPSDrive. You should see something similar to the following screenshot:
Close this window.
Go back to Management Studio, and right-click on one of your user databases.
Click on Start PowerShell. Note that this will launch another PowerShell session, with a path that points to the database you right-clicked from:
Note that the starting path of this window is different from the starting path when you first launched PowerShell in the second step. If you type
dirfrom this location, you will see all items that are sitting underneath the AdventureWorks2008R2 database.
You can see some of the items enumerated in this screen in SQL Server Management Studio's Object Explorer, if you expand the AdventureWorks2008R2 database node.
When PowerShell is launched through Management Studio, a context-sensitive PowerShell session is created, which automatically loads the SQLPS module. This will be evident in the prompt, which by default shows the current path of the object from which the Start PowerShell menu item was clicked.
SQL Server 2008/2008 R2 was shipped with a SQLPS mini-shell, also referred to as SQLPS utility. This can also be launched from SSMS by right-clicking on an object from Object Explorer, and clicking on Start PowerShell. This mini-shell was designed to be a closed shell preloaded with SQL Server extensions. This shell was meant to be used for SQL Server only, which proved to be quite limiting because DBAs and developers often need to load additional snap-ins and modules in order to integrate SQL Server with other systems through PowerShell. The alternative way is to launch a full-fledged PowerShell session, and depending on your PowerShell version either load snap-ins or load the SQLPS module.
In SQL Server 2012, the original mini-shell has been deprecated. When you launch a PowerShell session from SSMS in SQL Server 2012, it launches the full-fledged PowerShell session, with the updated SQLPS module loaded by default.
SQL Server is exposed as a PowerShell drive (PSDrive), which allows for traversing of objects as if they are folders and files. Thus, familiar commands for traversing directories are supported in this provider, such as dir or ls. Note that these familiar commands are often just aliases to the real cmdlet name, in this case, Get-ChildItem.
When you launch PowerShell from Management Studio, you can immediately start navigating the SQL Server PowerShell hierarchy.