{"id":1167,"date":"2011-07-05T00:00:00","date_gmt":"2011-07-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/powershell-eventing-and-sql-server-restores\/"},"modified":"2021-08-24T13:40:22","modified_gmt":"2021-08-24T13:40:22","slug":"powershell-eventing-and-sql-server-restores","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/powershell-eventing-and-sql-server-restores\/","title":{"rendered":"PowerShell Eventing and SQL Server Restores"},"content":{"rendered":"
\n

It all began one bright morning, when my good friend and PowerShell Jedi Ravikanth Chaganti (blog<\/a>| twitter<\/a>) \u00a0asked me if I had a PowerShell script \u00a0to 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<\/a>. However, it turned out the challenge he faced was not so simple, and he elaborated on his real problem:<\/p>\n

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.<\/p>\n

Now this<\/i> sounded interesting! But how to do it? Helloooo PowerShell Eventing…<\/p>\n

PowerShell Eventing<\/h2>\n

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<\/a>). However, my goal is not to explain what the PowerShell Eventing \u00a0feature is; I’m here to demonstrate how to implement an effective real-world solution using<\/i> that feature.<\/p>\n

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.<\/p>\n

Before we get started, I’ll explain that I modified Chad Miller’s (blog<\/a>|twitter<\/a>) original Invoke-Sqlrestore<\/b> 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<\/b> , inside the PoshTest.Psm1<\/b> module, and comes with additional SMO assemblies to import it directly into your\u00a0 PowerShell user profile.<\/p>\n

The Problem<\/h2>\n

I needed an automated and reasonably scalable way to restore 50 databases asynchronously, and be notified when each one was finished.<\/p>\n

Step 1 – Just Show a Message<\/h3>\n

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<\/a>, we find the available Events, including Complete<\/b>:<\/i><\/p>\n

\"1319-clip_image002.jpg\"<\/a><\/p>\n

Figure 1 – The available events on the Restore Class (click to enlarge)<\/b><\/p>\n

So I wrote some PowerShell to use that:<\/p>\n

$restore = new-object (\"Microsoft.SqlServer.Management.Smo.Restore\")  Register-ObjectEvent -InputObject $restore\u00a0 -EventName \"Complete\" -SourceIdentifier CompleteRestore -Action { Write-Host \"Restore Completed\"}\u00a0 | Out-Null\r\n<\/pre>\n
And tested it to make sure it works:<\/p>\n
\"1319-clip_image004.jpg\"<\/a><\/p>\n
Figure 2 – Our initial script, working fine. (click to enlarge)<\/b><\/p>\n
That all looked OK. So imagine my surprise when I tried to restore again, and saw this:<\/p>\n
\"1319-clip_image006.jpg\"<\/a><\/p>\n
Figure 3 – The same simple message script, but something’s gone wrong. (click to enlarge)<\/b><\/p>\n
Cannot subscribe to event. A subscriber with source identifier 'CompleteRestore' already exists.\r\n<\/pre>\n
\u00a0I realized I had created a CompleteRestore<\/b> subscriber in the SourceIdentifier<\/b> parameter of the Register-ObjectEvent<\/b> cmdlet, so I needed to unregister<\/i> it before I could run the cmdlet again:<\/p>\n
try { $restore.SqlRestore($server) }\r\ncatch {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 blablablabla\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n} finally {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Unregister-Event CompleteRestore\r\n}\r\n<\/pre>\n
Second Step – Running in an Asynchronous PowerShell Job<\/h3>\n
With my message script running smoothly, my second thought was “Neat, but it’s not much good without being asynchronous<\/i>“. If I have to restore 50 databases, it cannot be in a serialized way! So I tried :<\/p>\n
$server = \"Vader\"\r\n$dbname = \"TestPoshEventing_6\"\r\n$filepath = \"c:\\temp\\backup\\TestPoshEventing.bak\"\r\n$Realocatefiles = @{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_6.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_6.ldf'}\r\nStart-Job -Name \"Restore1\" -InitializationScript\u00a0 {Import-Module c:\\temp\\testes\\PoshTest.psm1 -Force} -scriptblock { Invoke-SqlRestoreEventing -sqlserver $args[0] -dbname $args[1]\u00a0 -filepath $args[2] -relocatefiles $args[3] -force } -ArgumentList $server, $Dbname ,$filepath ,$Realocatefiles\r\n<\/pre>\n
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<\/b> :<\/p>\n
$server = \"Vader\"\r\n$dbname = \"TestPoshEventing_6\"\r\n$filepath = \"c:\\temp\\backup\\TestPoshEventing.bak\"\r\n$Realocatefiles = @{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_6.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_6.ldf'}\r\n$job = Start-Job -Name \"Restore1\" -InitializationScript\u00a0 {Import-Module c:\\temp\\testes\\PoshTest.psm1 -Force} -scriptblock { Invoke-SqlRestoreEventing -sqlserver $args[0] -dbname $args[1]\u00a0 -filepath $args[2] -relocatefiles $args[3] -force } -ArgumentList $server, $Dbname ,$filepath ,$Realocatefiles\r\n\u00a0\r\nWait-Job $job | Receive-Job\u00a0\r\n<\/pre>\n
And the Oscar goes to… PowerShell! Everything now works just fine.<\/p>\n
Third Step -Showing a Message and the Database Name<\/h3>\n
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<\/b> element:<\/p>\n
Invoke-SqlRestoreEventing -sqlserver Vader -dbname \"TestPoshEventing_6\"\u00a0 -filepath \"c:\\temp\\backup\\TestPoshEventing.bak\" -relocatefiles @{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_6.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_6.ldf'} -force\r\nTestPoshEventing_6 Restore Completed\r\n<\/pre>\n
\"1319-clip_image008.jpg\"<\/a><\/p>\n
Figure 4 – The “Restore Complete” message, complete with the database name. (click to enlarge)<\/b><\/p>\n
Now you should hopefully be thinking, as I was, that because backgrounds jobs run in a different\u00a0 runspace, $dbname<\/b> will not be displayed when we put these two scripts together. How do we solve this?<\/p>\n
\u00a0Never fear! In this case, I used the -messagedata<\/b> parameter on Register- ObjectEvent<\/b>, and got the value we need using $event.Messagedata<\/b>:<\/p>\n
\u00a0Register-ObjectEvent -InputObject $restore\u00a0 -EventName \"Complete\" -SourceIdentifier CompleteRestore -Action { Write-Host \"$($event.MessageData) restore Completed\"} -MessageData $dbname | Out-Null\r\n<\/pre>\n
Now let’s run the function:<\/p>\n
$server = \"Vader\"\r\n$dbname = \"TestPoshEventing_6\"\r\n$filepath = \"c:\\temp\\backup\\TestPoshEventing.bak\"\r\n$Realocatefiles = @{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_6.mdf'; TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_6.ldf'}\r\n$job = \r\nStart-Job\u00a0 -InitializationScript\u00a0 {Import-Module c:\\temp\\testes\\PoshTest.psm1 -Force} -scriptblock { Invoke-SqlRestoreEventing -sqlserver $args[0] -dbname $args[1]\u00a0 -filepath $args[2] -relocatefiles $args[3] -force } -ArgumentList $server, $Dbname ,$filepath ,$Realocatefiles\r\nWait-Job $job | Receive-Job \r\n<\/pre>\n
… and watch the magic happening:<\/p>\n
\u00a0 \"1319-clip_image010.jpg\"<\/a><\/p>\n
Figure 5 – Asynchronous database restores, complete with “Restore Complete” messages for each database. (click to enlarge)<\/b><\/p>\n
Scaling out The code<\/h2>\n
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.<\/p>\n
\u00a0The 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<\/b> again, but with a twist: I passed the parameters as a property to PSObject<\/b> and used $Event.MessageData.<ProPertyName><\/b><\/p>\n
$pso = new-object psobject -property @{Server=$server;DbName=$dbname} \r\nRegister-ObjectEvent -InputObject $restore\u00a0 -EventName \"Complete\" -SourceIdentifier CompleteRestore -Action { Write-Host \"Server $($event.MessageData.Server), database $($event.MessageData.dbname) restore Completed\"} -MessageData $pso | Out-Null\r\n<\/pre>\n
And with that in place, let’s see how this code deals with restoring 2 databases:<\/p>\n
\u00a0$server = \"Vader\"\r\n$dbname = \"TestPoshEventing_20\"\r\n$filepath = \"c:\\temp\\backup\\TestPoshEventing.bak\"\r\n$Realocatefiles = @{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_20.mdf'; TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_20.ldf'}\r\n$job = Start-Job\u00a0 -InitializationScript\u00a0 {Import-Module c:\\temp\\testes\\PoshTest.psm1 -Force} -scriptblock { Invoke-SqlRestoreEventing -sqlserver $args[0] -dbname $args[1]\u00a0 -filepath $args[2] -relocatefiles $args[3] -force } -ArgumentList $server, $Dbname ,$filepath ,$Realocatefiles\r\n\u00a0\r\n$server = \"Vader\"\r\n$dbname = \"TestPoshEventing_21\"\r\n$filepath = \"c:\\temp\\backup\\TestPoshEventing.bak\"\r\n$Realocatefiles = @{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_21.mdf'; TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_21.ldf'}\r\n\u00a0\r\n$job1 = Start-Job -Name \"Restore2\" -InitializationScript\u00a0 {Import-Module c:\\temp\\testes\\PoshTest.psm1 -Force} -scriptblock { Invoke-SqlRestoreEventing -sqlserver $args[0] -dbname $args[1]\u00a0 -filepath $args[2] -relocatefiles $args[3] -force } -ArgumentList $server, $Dbname ,$filepath ,$Realocatefiles\r\n\u00a0\r\nWait-Job $job | Receive-Job\r\nWait-Job $job1 | Receive-Job\r\n<\/pre>\n
\"1319-clip_image012.jpg\"<\/a><\/p>\n
Figure 6 – Managing 2 Restore jobs with no trouble. (click to enlarge)<\/b><\/p>\n
\u00a0How Cool is that?!<\/p>\n
\u00a0But 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?<\/i>” And my answer is, “No… let’s use an XML file for that<\/i>“. First I created an XML file (restore.xml<\/b>) \u00a0with the following structure, and populated it with all the servers and databases I wanted to restore:<\/p>\n
\u00a0<?xml version=\"1.0\" standalone=\"yes\" ?>\r\n<config>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_8<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_8.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_8.ldf'<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_9<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_9.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_9.ldf'<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_10<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_10.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_10.ldf'<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_11<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_11.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_11.ldf'<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n<\/config>\r\n<\/pre>\n
And now, with the addition of the code below, I was able to scale out the final script to run Invoke-SQLRestoringEventing<\/b>:<\/p>\n
$xmldata = [xml] (Get-Content c:\\temp\\testes\\Restore.xml )\r\n$cmdblock = \"\"\r\n$cmdBlock_1 = \"\"\r\n[int] $counter = 1\r\nforeach( $Value in $xmldata.config.values) \r\n{\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $cmdBlock += \"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 `$server = \"\"$($value.server)\"\"; `n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 `$dbname = \"\"$($value.dbname)\"\"; `n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 `$filepath = \"\"$($value.filepath)\"\"; `n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 `$Realocatefiles = @{$($value.realocatefiles)};`n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 `$job_$($Counter) = Start-Job -InitializationScript\u00a0 {Import-Module c:\\temp\\testes\\PoshTest.psm1 -Force} -scriptblock { Invoke-SqlRestoreEventing -sqlserver `$args[0] -dbname `$args[1]\u00a0 -filepath `$args[2] -relocatefiles `$args[3] -force } -ArgumentList `$server, `$dbname ,`$filepath ,`$Realocatefiles ; `n\"\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $cmdBlock_1 += \"`n wait-job `$job_$($Counter) | receive-job ;\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $counter++\r\n\u00a0\r\n}\r\n\u00a0\r\n$cmdblockTotal = $cmdblock + $cmdBlock_1\r\n$scriptBlock = $ExecutionContext.InvokeCommand.NewScriptBlock($cmdBlockTotal)\r\ninvoke-command -ScriptBlock $ScriptBlock \r\n<\/pre>\n
And just to prove that it works…<\/p>\n
\"1319-clip_image014.jpg\"<\/a><\/p>\n
Figure 7 – PowerShell eventing, working it’s multi-server management magic. (click to enlarge)<\/b><\/p>\n
That’s all well and good, but then I discovered a much more elegant way to control the queue<\/a>. The first step was to change the \u00a0realocatefile tag in the XML to a hashtable syntax:<\/p>\n
<?xml version=\"1.0\" standalone=\"yes\" ?>\r\n<config>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_8<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>@{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_8.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_8.ldf'}<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_9<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>@{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_9.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_9.ldf'}<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_10<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>@{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_10.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_10.ldf'}<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n\u00a0 <Values>\r\n\u00a0\u00a0\u00a0 <Server>Vader<\/Server>\r\n\u00a0\u00a0\u00a0 <Dbname>TestPoshEventing_11<\/Dbname>\r\n\u00a0\u00a0\u00a0 <Filepath>c:\\temp\\backup\\TestPoshEventing.bak<\/Filepath>\r\n\u00a0\u00a0\u00a0 <Realocatefiles>@{TestPoshEventing= 'c:\\temp\\restore\\TestPoshEventing_11.mdf';TestPoshEventing_log = 'c:\\temp\\restore\\TestPoshEventing_11.ldf'}<\/Realocatefiles>\r\n\u00a0 <\/Values>\r\n<\/config>\r\n<\/pre>\n
.. And then add this code to my function parameters:<\/p>\n
\u00a0\r\n$queue.Enqueue($item)\r\n<\/pre>\n
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)<\/b> again :<\/p>\n
[hashtable] $x = Invoke-Expression ($Value.Realocatefiles)\r\n$pso = new-object psobject -property @{Server=$value.server;DbName=$value.dbname;Filepath=$Value.filepath;Realocatefiles = $x} \r\n$queue.Enqueue($pso)\r\n\u00a0\r\n<\/pre>\n
As you can see, I changed the XML to use Invoke-Expression<\/b> and converted the String<\/b> in realocatefiles<\/b> tag to a PowerShell HashTable<\/b>. Next, I added the-messagedata <\/b>parameter into\u00a0 Register-ObjectEvent<\/b> (This tip for how to show the “Restore Completed” message is thanks to Ravikanth.)<\/p>\n
So finally, using the handy MSDN code with a few changes, the script now looks like this:<\/p>\n
$maxConcurrentJobs = 5\r\n$xmldata = [xml] (Get-Content c:\\temp\\testes\\Restore.xml\u00a0 )\r\n$queue = [System.Collections.Queue]::Synchronized( (New-Object System.Collections.Queue) )\r\nforeach( $Value in $xmldata.config.values) \r\n{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [hashtable] $Realocatefiles = Invoke-Expression ($Value.Realocatefiles)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $pso = new-object psobject -property @{Server=$value.server;DbName=$value.dbname;Filepath=$Value.filepath;Realocatefiles = $Realocatefiles } \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $queue.Enqueue($pso)\r\n}\r\n\u00a0\r\nfunction RunJobFromQueue\r\n\u00a0{\r\n\u00a0\u00a0\u00a0 if( $queue.Count -gt 0)\r\n\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $job = Start-Job\u00a0 -InitializationScript\u00a0 {Import-Module c:\\temp\\testes\\PoshTest.psm1\u00a0 -Force} -scriptblock {Param ($pso);Invoke-SqlRestoreEventing -sqlserver $pso.Server -dbname $pso.dbname\u00a0 -filepath $pso.filepath -relocatefiles $pso.realocatefiles -force } -ArgumentList $queue.Dequeue()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 Register-ObjectEvent -InputObject $job -EventName StateChanged -Action { RunJobFromQueue; receive-job $event.MessageData.Id; Remove-Job $event.MessageData.Id } -MessageData $job | Out-Null\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0}\r\n\u00a0\r\nfor( $i=0;$i -lt $maxConcurrentJobs;$i++ )\r\n\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0 RunJobFromQueue\r\n\u00a0}\r\n<\/pre>\n
We now have complete control of job queues, and the scaling of the code is more elegant:<\/p>\n
\"1319-clip_image016.jpg\"<\/a><\/p>\n
Figure 8 – More elegant PowerShell eventing, still working it’s multi-server management magic. (click to enlarge)<\/b><\/p>\n
Even better, you still can work in the same session!<\/a><\/p>\n
Now I’m Just Showing Off<\/h2>\n
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]\u00a0 and load that module into your PowerShell user profile too. Once you’ve done that, a little change is required in Invoke-SQLrestoring<\/b>:<\/p>\n
$pso = new-object psobject -property @{Server=$server;DbName=$dbname} \r\nRegister-ObjectEvent -InputObject $restore\u00a0 -EventName \"Complete\" -SourceIdentifier CompleteRestore -Action { Import-Module ShowBalloonTip -force;Write-Host \"Server $($event.MessageData.Server), database $($event.MessageData.dbname) restore Completed\";Show-BalloonTip \"Server $($event.MessageData.Server), database $($event.MessageData.dbname) restore Completed\"} -MessageData $pso | Out-Null\r\n... And we need to adjust our script to run with PoshTestBallon PowerShell module (which you can download from the top of this article):\r\n$maxConcurrentJobs = 5\r\n$xmldata = [xml] (Get-Content c:\\temp\\testes\\Restore.xml )\r\n$queue = [System.Collections.Queue]::Synchronized( (New-Object System.Collections.Queue) )\r\nforeach( $Value in $xmldata.config.values) \r\n{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [hashtable] $x = Invoke-Expression ($Value.Realocatefiles)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $pso = new-object psobject -property @{Server=$value.server;DbName=$value.dbname;Filepath=$Value.filepath;Realocatefiles = $x} \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $queue.Enqueue($pso)\r\n}\r\n\u00a0\r\nfunction RunJobFromQueue\r\n\u00a0{\r\n\u00a0\u00a0\u00a0 if( $queue.Count -gt 0)\r\n\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $j = Start-Job\u00a0 -InitializationScript\u00a0 {Import-Module PoshTestBallon.psm1 -Force} -scriptblock {Param ($pso);Invoke-SqlRestoreEventingBallon -sqlserver $pso.Server -dbname $pso.dbname\u00a0 -filepath $pso.filepath -relocatefiles $pso.realocatefiles -force } -ArgumentList $queue.Dequeue()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 Register-ObjectEvent -InputObject $j -EventName StateChanged -Action { RunJobFromQueue; receive-job $sender.Id; Remove-Job $Sender.Id } | Out-Null\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0}\r\n\u00a0\r\nfor( $i=0;$i -lt $maxConcurrentJobs;$i++ )\r\n\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0 RunJobFromQueue\r\n\u00a0}\r\n\u00a0\r\n<\/pre>\n
Once you’ve done that, revel in your Power(Shell)…<\/p>\n
\u00a0<\/p>\n
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.<\/p>\n
In Closing<\/h2>\n
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 \u00a0PowerShell MVP and friend Max Trinidad (blog<\/a>|twitter<\/a>), who is also working on a really cool CodePlex project: SQLDevTools – PowerShell SQL Server Developers Toos)<\/a><\/p>\n
Happy PowerShelling!<\/b><\/p>\n
Credits<\/h3>\n
Thanks to all the PowerShell\u00a0 Jedis who helped me, Jeffery Hicks (blog<\/a>|twitter<\/a>), Marco Shaw (Blog<\/a>), Sean\u00a0 Kearney (blog<\/a>|twitter<\/a>) and especially\u00a0 Ravikanth ( instrumental in finishing this script), who all pointed me in the right directions to create this procedure.<\/p>\n
A special thanks goes to my friend and mentor Chad Miller (blog<\/a>|twitter<\/a>), who kindly gave permission to use his invoke-sqlrestore<\/b> function in my tests.<\/p>\n
Links for PowerShell<\/h3>\n