It all began one bright morning, when my good friend and PowerShell Jedi Ravikanth Chaganti (blog| twitter)  asked me if I had a PowerShell script  to restore databases. This sounded like a pretty simple process, and so I told him that what he needed was available on CodePlex in the form of SQLPSX. However, it turned out the challenge he faced was not so simple, and he elaborated on his real problem:

He actually needed to restore 50 databases in asynchronous mode and, having discovered that the Restore class had events, wanted to use those to trigger a message when the restore process finished.

Now this sounded interesting! But how to do it? Helloooo PowerShell Eventing…

PowerShell Eventing

Eventing is a feature built into PowerShell V2 which lets you respond to the asynchronous notifications that many objects support (as seen on the Windows PowerShell Blog). However, my goal is not to explain what the PowerShell Eventing  feature is; I’m here to demonstrate how to implement an effective real-world solution using that feature.

Of course, if you want to know more about what PowerShell Eventing is, then I suggest you read the links at the end of the article.

Before we get started, I’ll explain that I modified Chad Miller’s (blog|twitter) original Invoke-Sqlrestore function to use the complete Restore event for our purposes (with Chad’s kind permission, naturally). In the course of the article, I’ll show you step by step how I got the final solution, and you can download the finished script from the bottom of the article. The altered function is called Invoke-SqlrestoreEventing , inside the PoshTest.Psm1 module, and comes with additional SMO assemblies to import it directly into your  PowerShell user profile.

The Problem

I needed an automated and reasonably scalable way to restore 50 databases asynchronously, and be notified when each one was finished.

Step 1 – Just Show a Message

My first step towards Eldorado was to just show a “Restore Completed” message when a restore operation was finished. If we take a look at the MSDN information for the Restore Class, we find the available Events, including Complete:

Figure 1 – The available events on the Restore Class (click to enlarge)

So I wrote some PowerShell to use that:

And tested it to make sure it works:

Figure 2 – Our initial script, working fine. (click to enlarge)

That all looked OK. So imagine my surprise when I tried to restore again, and saw this:

Figure 3 – The same simple message script, but something’s gone wrong. (click to enlarge)

 I realized I had created a CompleteRestore subscriber in the SourceIdentifier parameter of the Register-ObjectEvent cmdlet, so I needed to unregister it before I could run the cmdlet again:

Second Step – Running in an Asynchronous PowerShell Job

With my message script running smoothly, my second thought was “Neat, but it’s not much good without being asynchronous“. If I have to restore 50 databases, it cannot be in a serialized way! So I tried :

Aaand…it didn’t work. Why not? Because background Jobs run in a different runspace, and so anything we send to output in the console won’t show up. To work around that, I needed to use Receive-Job :

And the Oscar goes to… PowerShell! Everything now works just fine.

Third Step -Showing a Message and the Database Name

The “Restore Completed” message I put together earlier is handy, but not actually that useful without knowing which database has been restored. To improve that, I added the $dbname element:

Figure 4 – The “Restore Complete” message, complete with the database name. (click to enlarge)

Now you should hopefully be thinking, as I was, that because backgrounds jobs run in a different  runspace, $dbname will not be displayed when we put these two scripts together. How do we solve this?

 Never fear! In this case, I used the -messagedata parameter on Register- ObjectEvent, and got the value we need using $event.Messagedata:

Now let’s run the function:

… and watch the magic happening:

 

Figure 5 – Asynchronous database restores, complete with “Restore Complete” messages for each database. (click to enlarge)

Scaling out The code

One of the main reasons why I use PowerShell is because of its inherent capacity to manage multiple servers with just a few lines of script. That is, scaling out my code is relatively easy. Which is just as well, because while the solution as it stands is fine for a test case, it’s not quite ready to deal with 50 databases efficiently.

 The first thing I needed to do was to add the server name into the message so that I knew exactly which database was being managed at each stage. For this I used -messagedata again, but with a twist: I passed the parameters as a property to PSObject and used $Event.MessageData.<ProPertyName>

And with that in place, let’s see how this code deals with restoring 2 databases:

Figure 6 – Managing 2 Restore jobs with no trouble. (click to enlarge)

 How Cool is that?!

 But now you’re thinking, “OK Laerte, that’s neat, but will I have to hard code all the servers and databases that I want to restore?” And my answer is, “No… let’s use an XML file for that“. First I created an XML file (restore.xml)  with the following structure, and populated it with all the servers and databases I wanted to restore:

And now, with the addition of the code below, I was able to scale out the final script to run Invoke-SQLRestoringEventing:

And just to prove that it works…

Figure 7 – PowerShell eventing, working it’s multi-server management magic. (click to enlarge)

That’s all well and good, but then I discovered a much more elegant way to control the queue. The first step was to change the  realocatefile tag in the XML to a hashtable syntax:

.. And then add this code to my function parameters:

In this simple case, the code only uses one parameter, but I needed tp use more for my function. Thankfully, this was easily solved this by using PSObject (custom objects) again :

As you can see, I changed the XML to use Invoke-Expression and converted the String in realocatefiles tag to a PowerShell HashTable. Next, I added the-messagedata parameter into  Register-ObjectEvent (This tip for how to show the “Restore Completed” message is thanks to Ravikanth.)

So finally, using the handy MSDN code with a few changes, the script now looks like this:

We now have complete control of job queues, and the scaling of the code is more elegant:

Figure 8 – More elegant PowerShell eventing, still working it’s multi-server management magic. (click to enlarge)

Even better, you still can work in the same session!

Now I’m Just Showing Off

So now the solution works, but what if we want a windows task bar balloon notification too, rather than just the console messages? Well, first of all, we have to download the appropriate module from Robertro Belo’s Blog (Sly PowerShell – Balloon tip notifications) [link deprecated]  and load that module into your PowerShell user profile too. Once you’ve done that, a little change is required in Invoke-SQLrestoring:

Once you’ve done that, revel in your Power(Shell)…

 

Can you see this? Can you feel the power? If you want to see the full solution, please feel free to download it and take a look.

In Closing

Well my friends, that’s the first time I’ve done anything with PowerShell Eventing, and I’m now bursting with ideas for what to do next, and I hope you are too. I will finish my article by paraphrasing  PowerShell MVP and friend Max Trinidad (blog|twitter), who is also working on a really cool CodePlex project: SQLDevTools – PowerShell SQL Server Developers Toos)

Happy PowerShelling!

Credits

Thanks to all the PowerShell  Jedis who helped me, Jeffery Hicks (blog|twitter), Marco Shaw (Blog), Sean  Kearney (blog|twitter) and especially  Ravikanth ( instrumental in finishing this script), who all pointed me in the right directions to create this procedure.

A special thanks goes to my friend and mentor Chad Miller (blog|twitter), who kindly gave permission to use his invoke-sqlrestore function in my tests.

Links for PowerShell

• Ravikanth Chaganti Blog
• Hey, Scripting Guy!Blog
• Jeffery Hicks – The Lonely Administrator
• Sean Kearney Blog
• Don Jones on PowerShell
• Jonathan Medd’s Blog

Links for PowerShell with SQL Server

• Chad Miller – Sev17
• Aaron Nelson (SQLVariant) Blog
• Jorge Segarra – SQL University Blog
• Max Trinidad – The PowerShell Front