“Everyone Stand Back! I’m Learning PowerShell!”

I keep telling folks, “Learn new stuff” and “then blog about it!” I figured it’s time I take my own advice.

I’m reading Learn Windows PowerShell in a Month of Lunches by Don Jones (@concentrateddon). I’m actually reading the first edition. I bought the book a while back and I’m just now reading it. Why? I got shamed on Twitter for using Azure CLI. It’s for the best, I promise. I’ve been needing to learn PowerShell. And Hadoop. And Machine Learning (I’ve been helping my Data Driven co-host, Frank La Vigne [@Tableteer] some by tech-editing his Artificially Intelligent column in MSDN Magazine).

Connecting to a SQL Server Instance

In the first lesson / lunch, Don shares how to use PSDrives. PSDrives allow you to do some very cool things in PowerShell – like connect to the registry and navigate it as you would the file system.  In this section, Don mentions you can connect to SQL Server and navigate the contents of an instance in a similar fashion. I had to figure this out!

But when I tried – being a (literal) Day One n00b – I got errors running commands I found online. For example, running:

$cred=Get-Credential
New-PSDrive -Credential $cred -Name dcBase -PSProvider SqlServer -Root ‘SQLSERVER:\SQL\vmDC\BASE\’

resulted in an error:

New-PSDrive : Cannot find a provider with the name ‘SqlServer’.

I searched for an answer and had difficult time locating what I was doing wrong. One key to learning is making the problem give up before you do. After an inordinate amount of time, I discovered I’d not loaded the sqlps module.

Now, if you’ve been using PowerShell for a while, you already have your environment configured to automagically load sqlps. If you’re a n00b, though, you may not. And I am a n00b.

Also, for people I’ve encouraged to start blogging: Here’s a topic. Right here. I picked up a book on a topic I know nothing about, starting reading, tried something on a tangent, made the problem give up before I did, and BAM! Instant topic.

My Fix

Even though I’m no Mike Fal (@Mike_Fal), Chrissy LeMaire (@cl), Rob Sewell (@sqldbawithbeard), or Aaron Nelson (@sqlvariant), I ran this one down and figured it out.

Import-Module sqlps
$cred=Get-Credential
New-PSDrive -Credential $cred -Name dcBase -PSProvider SqlServer -Root ‘SQLSERVER:\SQL\vmDC\BASE\’

I start the script I ran before with “Import-Module sqlps”. When I run this script, I’m prompted for credentials (as before – this is what the “$cred=Get-Credentials” line does – it creates a variable to hold my credentials):

Once I enter my credentials and click OK, it works!

So I saved it. I’m using the PowerShell ISE, so I opened the Scripts window by pressing Ctrl+R, copied my script and saved it:

Now I won’t ever have to figure that out again.

One Other Thing

In experimenting, I also learned that when connecting to local instances – that is, instances of SQL Server on the same server I’m running PowerShell – supplying credentials is unnecessary – at least when I am system administrator and a sysadmin on the SQL Server instance.

Browsing my SQL Server Instance

I next attempted to connect to my newly-mapped PSDrive and experienced another n00b moment. My command?

cd dcBase

The error?

cd : Cannot find path ‘C:\dcBase’ because it does not exist.

What the heck? I’m not trying to change the file system directory! Oh wait, yes I am. I need to type:

cd dcBase:

Once I do that, I can list available artifacts:

Now I can navigate! I can change the “directory” to Databases and execute an “ls” command to list the contents of the Databases “folder”:

Conclusion

This really isn’t the conclusion – I’m just getting started!

I hope you take away one or two things from this post:

1. You do not have to be an expert in technology to blog about it. I am definitely not a PowerShell expert, and yet here I am, blogging about it.
2. You see one way to create a PSDrive for a SQL Server instance and connect to it, along with a couple ways to not do those things.

I hope this helps…