Neat Trick – SQL Powershell Provider

This is something rather basic but certainly worth mentioning.

Once you load the powershell SQL Provider (code below) you can access a sql servers DB’s like so,

cd SQLServer:\sql\servername\default\databases\dbname

That is to say, you change to the provider, SQLServer, go to the DB engine side of things, sql, the server, the instance which in this case is the default, then in to databases, and then the database name.

well, who wants to type that out all the time? tab complete works, but i’ve run in to problems doing it right after the server name.

So, if you are like me and often just want to poke around the databases, you can make a shortcut like so.

New-PSDrive –name ServerSQL –root SQLServer:\sql\server\default\databases –psprovider sqlserver

so then, to get to that servers SQL DB’s you can just do


cd servername:

Here is the code you should have in your profile to load up the SQL stuff.

$ErrorActionPreference = "Stop"


if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
    throw "SQL Server Provider for Windows PowerShell is not installed."
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

# Set mandatory variables for the SQL Server provider
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

# Load the snapins, type data, format data
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml


About jrich

I am the Solutions Architect for Apex Learning in Seattle WA. I've been working with computers since I was 13. Started programming when I was 14. Had my first IT job as tech support at an ISP at the age of 15 and became a network admin at the age of 17. Since then I've worked at a variety of small to mid size companies supporting, maintaining and developing all aspects of IT. Mostly working with Windows based networks but have recently been working with Solaris system as well. I created this blog mostly as a place for me to take my own notes, but also share things that I had a hard time finding the info for.

Posted on September 29, 2010, in SQL Server, WMF (Powershell/WinRM). Bookmark the permalink. 2 Comments.

  1. When I initially left a comment I seem to have clicked the
    -Notify me when new comments are added- checkbox and now every time a
    comment is added I get four emails with the same comment. Perhaps
    there is a means you can remove me from that service? Thank

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: