.::: Check Progres Status Backup & Restore Progress in % Percent MSSQL SQL Server :::.

A. Backup

1. Query 1

SELECT @@servername as ServerName, command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

2. Query 2

SELECT @@servername as Servername,
    session_id AS SPID,
    command,
    s.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) s
WHERE
    r.command LIKE 'BACKUP%'
    AND r.percent_complete > 0;
 

B. Restore

1. Query 1    

SELECT @@servername as ServerName, command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
 
2. Query 2

SELECT @@servername as Servername,
    session_id AS SPID,
    command,
    s.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) s
WHERE
    r.command LIKE 'RESTORE%'
    AND r.percent_complete > 0;

 

3. Query complete

SELECT 
    @@SERVERNAME AS ServerName,

    LTRIM(RTRIM(
        SUBSTRING(
            s.text,
            CHARINDEX('restore database', LOWER(s.text)) + LEN('restore database'),
            CHARINDEX('from disk', LOWER(s.text)) -
            (CHARINDEX('restore database', LOWER(s.text)) + LEN('restore database'))
        )
    )) AS DatabaseName,

    command, 
    CAST(percent_complete AS DECIMAL(5,2)) AS percentx, 
    start_time, 

    DATEADD(ms, estimated_completion_time, GETDATE()) AS ETA_Finish, 

    CONVERT(varchar(8), 
            DATEADD(ms, total_elapsed_time, 0), 
            108) AS Elapsed_Time, 

    CONVERT(varchar(8), 
            DATEADD(ms, estimated_completion_time, 0), 
            108) AS Remaining_Time, 

    CONVERT(varchar(8), 
            DATEADD(ms, 
                total_elapsed_time / 
                NULLIF(percent_complete / 100.0, 0), 
                0), 
            108) AS ETA_Total_Time 

FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');