Category: Powershell

Shawn Melton provides an introduction to various ways to interact with a SQL Server instance via Powershell:

The most commonly known cmdlet out of this module is, Invoke-Sqlcmd. This is generally thought of as a PS replacement for the old sqlcmd command-line utility, that to date is still available in currently supported versions of SQL Server. You utilize this cmdlet to execute any T-SQL query that you want against one or multiple instances. The advantage you get using Invoke-Sqlcmd over the command-line utility is the power of handling output in PS. The output from the cmdlet is created as a DataTable (System.Data.DataRow is the exact type).

This is a good overview of the different methods available.

Steve Jones shows us how to implement a hashtable in Powershell:

With that code, I could easily solve the puzzle. However I was struck by the various ways I work with the hash tables. I use braces, {}, to declare the table. I use brackets, [], to access elements and then parenthesis, (), when calling methods. All of that makes programming sense, but it’s something to keep in mind, especially as those three marks mean different things in Python.

Hashtables are easy to implement in Powershell and are extremely useful.

Mike Fal has a function for managing SQL Server startup parameters:

Looking back at the previous blog post, changing the the startup parameters through the SMO is pretty easy with the ManagedComputer class. In some ways, it is too easy. As Shawn calls out, you could easily overwrite the full string and remove the startup locations for your master database (and breaking your instance). This is where tool building can be such an aid, because by wrapping the change code in a function, we can build some safety mechanisms to protect us (or others) from doing harm when trying to make this sort of change. The function I wrote is not terribly long, but I’ll spare you the whole thing by letting you view it on GitHub. We’ll use our time better by going over how I constructed it while focusing on some of my tool building principles.

Thanks to Mike for making that available to the community.

Steve Jones show us how to schedule Powershell tasks:

One of the things I needed to do with my SQL Saturday download process was run this automatically. I don’t want to remember to do this, so I decided to set this up on a schedule.

I ran a quick search and it seemed to be a simple process. Essentially I run the Task Scheduler application and then call my script as a parameter to the PowerShell exe.

Windows Task Scheduler isn’t fantastic (and they seem to mess with it in every version), but it does let you run Powershell scripts easily.  Bonus points for those of you using at for scheduling.

Mike Fal shows us how to move the master database:

Last week I blogged about how you can use Smo.ManagedComputer class to update service accounts for your SQL Services. My friend Shawn Melton(@wsmelton) decided to explore the class a little more and discovered how you can change your startup parameters using this class. Shawn’s examples are focused around adding common trace flags to your instance, which is a typical practice. However, let us look at using this to move master.

A word of warning:  don’t move the master database unless you really need to.

Steve Jones shows how to end a loop early:

That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I’d just as soon use a break statement, but there are many ways to skin a cat.