0

I have a Powershell script that restores a database using the SqlRestoreAsync method.

I have a decent size database which I'm restoring (~55Gb) and I'm trying to find the progress of the restore process.

I know that if I run the restore through a T-SQL command then I can view the progress of the restore by querying a few DMV's or with the following query:

SELECT r.session_id
    , r.command
    , CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
    , CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
    , CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
    , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
    , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
    , CONVERT(VARCHAR(1000), (
            SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE 
                        WHEN r.statement_end_offset = - 1
                            THEN 1000
                        ELSE (r.statement_end_offset - r.statement_start_offset) / 2
                        END)
            FROM sys.dm_exec_sql_text(sql_handle)
            )) AS [SQL]
FROM sys.dm_exec_requests r
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');

This query works fine for T-SQL triggered restores (or SSMS ones), but doesn't show up the restore progress for the restore triggered by the Powershell script.

I can see my database in SSMS's Object Explorer as being in "Restoring" state, so I know it's working, but .. is it running?

The restore process running this in Async mode seems to take a bit more than when running it through T-SQL, so far twice as much and still not finished.

Is there something I'm missing? Any help would be appreciated.

0

2 Answers 2

5

I don't see anything obvious about why this command won't be showing up like any other restore command, unless it is busy doing something other than RESTORE DATABASE.

I suggest identifying the session_id PowerShell is using (you should be able to see in sys.dm_exec_sessions or sys.dm_exec_connections), or you could change the script to obtain it and print it to the console. Then change your filter to:

SELECT command, status, percent_complete, 
  wait_type, last_wait_type, wait_resource,
  blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = <x>;

This will help you verify:

  • if the command is actually a RESTORE or if Powershell is actually doing something else first
  • current status
  • percent complete, if that is available (it isn't always)
  • what it's waiting on (and if you run it repeatedly, if this is changing)
  • if your restore is actually being blocked by anything
0

Try listing the active processes:

SELECT session_id as SPID
     , command
     , a.text AS Query
     , start_time
     , percent_complete
     , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
--WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

And see if you can spot the process. (My guess is that the command is an sp_ExecuteSQL)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.