The problem of obtaining the execution status of the SQL server restore job

Question

I want to use the script from http://www.wisesoft.co.uk/articles/tsql_backup_restore_progress.aspx to get information about the progress of a specific SQL-Server recovery job . To achieve this, I modified it a bit:

SELECT command, s.text, start_time, percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.command = 'RESTORE DATABASE' and r.database_id = db_id('database_name') 

Unfortunately, this does not work, because r.database_id never matches db_id ('database_name') . What is the reason for this? What is wrong with this SQL statement? This script works fine for 'r.command = BACKUP DATABASE' .

Are there other T-SQL-based capabilities for getting the status of a specific SQL server task?

Answer 1

when starting BACKUP, the database is online and the command is launched inside the database. But when you run RESTORE, the database cannot be online (you are actively restoring it, therefore it cannot be online), so the command cannot be executed in the context of the specified database.

So instead of popping DB_ID to guess which one is yours, just slip @@SPID before executing the RESTORE command on SqlConnection, and then look at the request with this session_id : this will be your RESTORE request.