Display Progress while Restoring Database with PowerShell and SMO

Question

I have a script for restoring a database with PowerShell and SMO. Now I know that I can pass a event handler into PercentComplete on the restore object and get the progress of the restore as it happens. The problem is I don't know how to create a event handler and pass it a function in PowerShell? I can do it in C#

restore.PercentComplete += new PercentCompleteEventHandler(restore_PercentComplete);

static void restore_PercentComplete(object sender, PercentCompleteEventArgs e)
{
  System.Console.WriteLine("{0}", e.Percent);
}

Any help would be appreciated.

Thank You.

Answer 1

After some deeper searching I finally found it in the documentation. To add event handlers you need to do the following:

Import the relevant assemblies;

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

Now to create the event handler you need to declare it with an inline function;

$percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-Host "Restored " $_.Percent "%" }
$completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host "Database " $databasename " Created Successfuly!" }

Now the final step is to add the event handler to the object you are working with. Normally in C# you just do the following;

restore.PercentComplete += new PercentCompleteEventHandler(restore_PercentComplete);
restore.Complete += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(restore_Complete);

This will not work in PowerShell script, what you need to do is use the generated function for adding events. The function name is the EventHandlerName with "add_" appended to the beginning of it, like so;

$dbRestore.add_PercentComplete($percentEventHandler)
$dbRestore.add_Complete($completedEventHandler)

Hope this helps anyone else trying to do this!

Answer 2

You can do it async style with pseventing in v1 powershell - http://pseventing.codeplex.com. check on progress whenever you like instead of waiting. v2 powershell has its own eventing.

i include a script in pseventing to do exactly what you say, except in a background fashion.

-Oisin