Viewing the remaining restore time for rebuilding log-shipping

I sometimes need to rebuild the log-shipping for a database and if it is a large database the windows dialogs only display the percentage during the backup phase of the rebuild. Once the process starts the restore of the database, the dialog just sits and spins until it is complete. Below is a query that will allow you to see the percentage of completion during the restore process and an estimated time in milliseconds (I will update the query to seconds in a future version)

First we need to find the SPID for the process that is performing the RESTORE:

You will be able to identify this task by the login that the restore is running under. Typically this is an account name something like “log.shipper” or it could be a domain account set up specifically for this task.

Once we have identified the process and SPID it is running under, plug that number in the following query (replace the 90 number below with the actual SPID in your case).

NOTE: This query will work for ANY type of restore, whether rebuilding log-shipping or restoring a database (the latter will display the percentage complete in the dialog).

This SQL will allow you to just plug in the database name and it will find the SPID for you and return the results. It uses the famous Adam Mechanics sp_WhoIsActive. If you are not using this utility, you need to go here now Who is Active.

otherwise you can run it manually as displayed below:


Views – 2109