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"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $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
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

Advertisements

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
    you!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: