SCRIPT TO CHECK DATABASE BACKUP OR RESTORE STATUS

USE master

GO




SELECT

req.session_id,

database_name = db_name(req.database_id),

req.status,

req.blocking_session_id,

req.command,

[sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, (

(

CASE req.statement_end_offset

WHEN - 1 THEN Datalength(txt.TEXT)

ELSE req.statement_end_offset

END - req.statement_start_offset

) / 2

) + 1),

req.percent_complete,

req.start_time,

cpu_time_sec = req.cpu_time / 1000,

granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),

req.reads,

req.logical_reads,

req.writes,

eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),

elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),

remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),

eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),

wait_type,

wait_time_sec = wait_time/1000,

wait_resource

FROM sys.dm_exec_requests as req WITH(NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt

WHERE req.session_id>50

AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')