Every now and then we all need to perform backup or restore, for large databases when we need to perform backup/restore, DBA often got ask how long a backup will take, which we often estimate using the previous history. But when user ask how long a restore will be completed, that will be a difficult to answer. Normally DBA will put in the option "status" in the restore script to display the restore progress, but you still cannot say when it can complete, the best you can only answer its up to 45% for example.

The following script can tell you how soon SQL server think it will complete the backup or restore base on its throughput, which is pretty accurate. Using that, we can now answer the question of when the restore will be completed if we are in the situation of disaster.

USE master
GO
SELECT
    session_id as SPID,
    CONVERT(VARCHAR(50),start_time,100) AS start_time,
    percent_complete,
    CONVERT(VARCHAR(50),dateadd(second,estimated_completion_time/1000, getdate()),100) as estimated_completion_time,
    command, a.text AS Query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'