Davide Mauri Blog

SQL Server 2008 has a strong integration with PowerShell but actually it also provide a custom shell, SQLPS, which is somehow limited for me. Well actually is limited more in general speaking, since it is called "minishell".

"SQLPS.exe is a Minishell (also called “custom shell”). It is a form of pre-packaging of Powershell functionality, and it is available to anyone who wants to do this (make-shell). It is regular Powershell, albeit with limitations that the Powershell team decided to impose on it – it is a ‘closed’ shell, which doesn’t allow adding other snapins.

We are shipping SQLPS to make life of our DBA’s a whole lot easier. If they need to have quick access to the SQL providers, assemblies, cmdlets, default security settings, everything is there. We could have possibly done this through a startup script but not everything can be accomplished this way. We are changing the default security settings, without affecting the settings for overall Powershell. Minishells have their own settings."

http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx

Of course it give PowerShell access right out of the box and requires no configuration, but I need something more developer friendly when I have to write complex powershell scripts.

More in generale, when I use PowerShell I'd like to use my preferred IDE (actually PowerGUI). Is it possible to configure any shell other than SQLPS to have the same behaviour of that one? Fortunately yes!

Is just a matter of adding the snapins so that we can use the SQL: drives

# Manual procedure
Get-PSSnapin -registered
Add-PSSnapin SqlServerCmdletSnapin
Add-PSSnapin SqlServerProviderSnapin

we can also automatize everything:

# Verbose command
Get-PSSnapin -Registered | Where-Object { $_.Name -like "SqlServer*" } | ForEach-Object { Write-Host "Adding Snapin " + $_.Name; Add-PSSnapin $_.Name }

# Contracted version:
# gsnp -Registered  | ? { $_.Name -like "SqlServer*" } | % { "Adding Snapin " + $_.Name; asnp $_.Name }

then we also need to make sure that the default formating when dealing with SQL Server object is the one used by SQLPS. Again all we need to do is just register formating and format type data:

# Load Formatting Types Data
Update-TypeData "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Types.ps1xml"
Update-FormatData "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLProvider.Format.ps1xml"

That's it! We can now enjoy SQL Server through our preferred PowerShell shell! SQL PSDrive, CmdLets and everything is ready to be used :-)