Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Display Progress while Restoring Database with PowerShell and SMO

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.

like image 891
Lukasz Avatar asked Aug 12 '10 16:08

Lukasz


People also ask

How do I check the progress of a database restore?

Open SSMS, right click on a database then select Tasks > Restore. A screen similar to the below image will open. After you select all of the restore options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the restore.

How can I check SQL Server backup progress?

In SQL Server 2016 and later versions, you can use XEvent backup_restore_progress_trace to track the progress of backup and restore operations. You can use the percent_complete column of sys. dm_exec_requests to track the progress of in-flight backup and restore operations.

How can I tell when a SQL Server database was restored?

We get the following database restoration history in my environment. restore_date: It shows the database restoration date. destination_database_name: We can get the destination database name using this column. user_name: it gives user name that performed the restoration for that particular database.


1 Answers

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!

like image 84
Lukasz Avatar answered Oct 17 '22 15:10

Lukasz