Tag: SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was looking at some sample code the other day and it looked like this.

cd sqlserver:\sql\localhost\default\databases

This allows you to browse the list of databases on your local instance. However, this is for a default instance, which I don’t have on this host. How can I get to a named instance? Usually I connect as .\SQL2016, so where does that fit in PowerShell?

The format you see above is for the SQLServer provider, which is provided as part of the SQLServer PoSh module (or SQLPS if you haven’t adopted the new cmdlets).

If I start at the SQLServer:\ node, I see this:

Let’s drop into the SQL node and see that.

At this point, I see my localhost and my local computer name. These are really the same thing. We can see that by querying each of them.

In my case, I like to type fewer characters, so I’ll look at the Plato node. If I change here, I can also change to an instance, as shown here.

Now I can see the databases by changing to that path and getting the directory.

Thus, we can see that to query an instance, we use this path:

SQLServer:\SQL\Host\instance

If you want to use the default instance, then use “default”.

A simple query path, but one that some people might wonder about, substituting the named instance for localhost, and not realizing that “Default” means just the default instance.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was editing an article on PoSh and learned something cool. I can use Grid-View to surface an object in a pipeline and then change the items passed through to the next command.

Here’s how it works. Suppose I have a simple comment, like Get-SqlDatabase. I can run this and get a series of database. (command and output shown here).

Get-SqlDatabase –ServerInstance .\SQL2014

I get a bunch of data, but I don’t want all the items. For example, maybe I just want the “SimpleTalk” databases.

Get-SqlDatabase -ServerInstance .\SQL2014 | Where-Object { $_.Name -like ‘SimpleTalk*’}

I don’t want to edit this Where-Object clause all the time. I want something simple to easily fix this.

Enter Out-GridView

Instead of changing my filter, I can do this graphically. I can pipe my output to Out-Gridview, and I’ll get this.

All of my output data appears. No big deal, right? Is this useful? It sure is. Look at the filter item above. I can type in there, and my data is filtered.

How can I use this? Suppose my code is this:

PS SQLSERVER:\SQL\Plato\SQL2016\Databases> Get-SqlDatabase -ServerInstance .\SQL2014 | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption Default –Script

Here I’m getting a list of databases, passing them to Out-GridView, and then sending the results to Backup-SqlDatabase, which will return a script to back up all my databases.

I’ll run this, and then enter a filter in the grid.

I can now highlight all these rows (if that’s what I want).

This time I have an “OK” button in the lower right corner of the grid.

When I click “OK”, my filtered list is returned to the pipeline and send to the backup command.

This is a quick way to work with the parameters in your pipeline in an ad hoc way. Use Out-GridView to filter and select the rows you want to return to the rest of your script.