Can we Use the SQL Server Mini-Shell Version of Windows PowerShell

[Prophet]

Yes, We can use the SQL Server with Mini Shell Version of Windows Power Shell, but i will explain this with some different scenario.

If you have noticed the Exchange Windows PowerShell tool seems different this is because they created a custom Windows PowerShell profile that loads the Exchange snap-in and creates a couple of special functions. This is designed to make it easier for you to use Windows PowerShell to manage Exchange 2007.

The version of Windows PowerShell that is used by the SQL team in SQL 2008 is the same version of Windows PowerShell that is included on the disk with Windows Server 2008—it is Windows PowerShell 1.0. The reason some commands are missing and other commands are added is because the SQL team created what is called a "mini-shell."

It’s the “mini-shell” that makes using Windows PowerShell with SQL Server instances even easier. We tooled around a bit inside the structure that this provider has, learned about the objects that it exposes and how you can get and set properties, and even ran several commands that the provider includes. But now it is time to really put this thing to work. We will see how you can use Windows PowerShell to perform some useful tasks, working through some practical applications.

[Prophet]

First let's talk about MiniShells and why they exist. During the Vista reset, there was a great deal of anxiety about .NET versioning and general angst about instability arising from plugin models where code coming from the various sources run in the same process. The SQL Server team created a set of mappings for the SMO object model so that they can be treated exactly like a drive letter. In other words, instead of having to set a variable to the instance and database objects that are displayed, you can just use the cd (change directory) shortcut to move around. Basically, you are dropped right into a SQL Server Instance, moving around the hierarchy of objects by using a simple cd command. Imagine the case where you load plugins from 10 different sources and then something goes wrong - who is responsible? Who do you call for support?

MiniShells allow teams to address these issues by creating fixed execution environments that built in our labs and fully tested/verified before release. If you have a problem with a SQL PowerShell and call PSS, the first thing they are going to do is to have you try to reproduce the problem using the SQL MiniShell. (NOTE: In my experience, 9 out of 10 times that you have a problem with multiple plugins in a process comes from bad memory management - a problem largely [but not completely] managed out of existence by the CLR.)

The first thing we have to do is obtain the directory of all the databases on an instance. Substituting the server name in the serverName part of this string and instance name (use default if it is the default instance) in the instanceName part of this string, type this command in the Windows PowerShell for SQL Server provider:

DIR "SQLSERVER:\SQL\serverName\instanceName\Databases"

Now let us add in a where-Object Windows PowerShell cmdlet to locate only those databases for which the backup is greater than a day:

DIR "SQLSERVER:\SQL\serverName\instanceName\Databases" |
where-Object {((get-Date)-($_.LastBackupDate)).days -gt 1}

You can see that the Where-Objectcmdlet is followed by a set of braces. This encloses what we want to do with the collection of items to the results of the dir command.

[Prophet]

Installing SQL Server Support

You install the software required to run Windows PowerShell scripts by using SQL Server Setup. SQL Server 2008 Setup installs the following Windows PowerShell components when you select either the client software or the Database Services nodes:

1. Windows PowerShell 1.0, if Windows PowerShell is not already present on your computer.
2. The SQL Server snap-ins. The snap-ins are dll files that implement two types of Windows PowerShell support for SQL Server:
   
   • A set of SQL Server cmdlets. Cmdlets are commands that implement a specific action. For example, Invoke-Sqlcmd runs a Transact-SQL or XQuery script that can also be run by using the sqlcmd utility, and Invoke-PolicyEvaluation reports whether SQL Server objects comply with policy-based management policies.
   • A SQL Server provider. The provider lets you navigate the hierarchy of SQL Server objects using a path similar to a file system path. Each object is associated with a class from the SQL Server Management object models. You can use the methods and properties of the class to perform work on the objects. For example, if you cd to a databases object in a path, you can use the methods and properties of the Microsoft.SqlServer.Managment.SMO.Database class to manage the database.
3. The sqlps utility that is used to run Windows PowerShell sessions that include the SQL Server snap-ins.

The SQL Server 2008 version of SQL Server Management Studio supports starting Windows PowerShell sessions from the Object Explorer tree. The SQL Server 2008 version of SQL Server Agent supports Windows PowerShell job steps.