Problem getting the progress status of a 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 progress information of a specific running SQL-Server restore job. To achive this, I have changed it a little 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 doesn't work because r.database_id is never equal to db_id('database_name'). What is the reason for that? What is wrong with this SQL statement? This script works fine for 'r.command = BACKUP DATABASE'.

Are there other T-SQL based possibilities to get the progress status of a specific SQL-Server job?

Answer 1

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

So instead of poking at the DB_ID to guess which one request is yours, simply sneak the @@SPID before executing the RESTORE command on the SqlConnection and then look at the request with that session_id: that it will be your RESTORE request.