Art of the DBA Rotating Header Image

October 1st, 2015:

A Month of SQLPS – Common Powershell cmdlets in SQLPS

Oct 1st, 2015
by Mike Fal.
No comments yet

One of the reasons to create a provider is so administrators have a common interface for using a section of the Windows ecosystem. Whether it’s the file system, the registry, or some other slice of the stack, you should be able to use a provider to browse, explore, and manipulate that part of Windows in the same way as you can in other providers. Because each part of the ecosystem is different, each provider will have its own way of functioning. A file or directory is simply not the same as a registry key. This means that some cmdlets will be implemented and some will not, all depending on how the provider is built.

With the SQL Server provider, there are only a handful of common cmdlets implemented:

  • Get-Location: Gets the current node.
  • Set-Location: Changes the current node.
  • Get-ChildItem: Lists the objects stored at the current node.
  • Get-Item: Returns the properties of the current item.
  • Rename-Item: Renames an object.
  • Remove-Item: Removes an object.

None of these are surprise, as they provide most of the basic navigation and retrieval of the provider. You probably know these cmdlets better by their aliases:

Set-Location SQLSERVER:\SQL
cd SQLSERVER:\SQL

Get-Location
pwd

Get-ChildItem SQLSERVER:\SQL\localhost\DEFAULT\Databases
dir SQLSERVER:\SQL\localhost\DEFAULT\Databases

As you can probably guess, if these cmdlets weren’t included, we’d have a pretty tough time getting around the provider. They are also core to our provider experience, so most users don’t even think about them. Having the cmdlets may not seem like a big deal, but without them we’d be stuck. It’s all about that common navigation experience.

Of these cmdlets, Get-ChildItem is most commonly used to work with objects in the provider. We’ve already covered how we can combine Get-ChildItem with either Select-Object or Format-Table to view specific properties of our objects. Add to this Get-Item, which covers the same function, just up a level, allowing us to work directly with a specific object:

Get-Item SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2012 | Format-List Name,Version,CompatibilityLevel,LastBackupDate,ActiveConnections

SQLPS-4-1

This small handful of functions let us get around. We can use Set-Location to browse to the path we need to work in, then leverage Get-Item and Get-ChildItem to acquire objects and work with them. The benefit here is we can use the same syntax for reviewing database objects, files, or environment variables. This common experience eases the use of Powershell and helps administrators move from one stack to another.

There are two other cmdlets on this list that I want to talk about, along with another one that is conspicuously missing. What we’ve covered here handles navigation, but the remaining cmdlets are more about object manipulation. In my next post, we’ll dive a little into those, why they are there, and what’s missing.