Monitor status of backup and restore progress using T SQL query in SQL Server

We will use SQL query to monitor status of database backup and restore progress. Mostly DBA’s do backup and restore through job or using some utility, at that time we were unable to find the status of backup and restore. If the database size is big we just refresh database and see when it will be completed. There is a easiest way to find the status using SQL query. We will use DMV – sys.dm_exec_requests to find the progress. This script will work for any backup or restore that is currently running regardless of method is being used to run the backup or restore.

SELECT session_id as SPID, command, aa.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) aa

WHERE r.command in('BACKUP DATABASE','RESTORE DATABASE')

select GETDATE() as 'Current Time'

percent_complete column is displaying the percent status as completed and  estimated_completion_time will give you the estimated time of task complettion