Determine or Estimated time (ETA) of Backup & Restoration

As a DBA you may face a few common questions whenever someone accidentally deleted some database objects or the whole database.

“How long-time SQL Server will take to complete the database Backup or Restore? Yes DBA speaks out!! move your jaw and tell something!!”

Performing database refresh activity, database migration activity, or any adhoc activity where DBA is handling a huge size database need to answer higher authorities or managers about the estimated time amount to do the activity.

In this post, we will try a simple script to get the estimated amount of time that SQL Server is going to take to complete database Backup or Restore.

This script can be used to find the estimated time of backup and restore that is on progress in your SQL server. This script is applicable for SQL Server 2005 and above. While your backup and restoration activity is going on, execute the below script.

/*
Author: Chris Yates
Original link: http://www.sqlservercentral.com/blogs/the-sql-professor/2017/01/26/backuprestorewhats-my-status/
*/

-- By checking only for the restore and backup command lines you will be able to quickly identify your session id
-- and get an approximate ETA and percentage complete. You can tinker of course with the estimations
-- if you’d like or pull back more fields. This is just a simple technique in utilizing a helpful DMV to provide info quickly.

SELECT  
    r.session_id AS 'Session ID',
    r.command AS 'Command',
    r.start_time AS 'Start Time',
    r.status AS 'Status',
    CONVERT(NUMERIC(6, 2), r.percent_complete) AS 'Percent Complete',
    CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time , GETDATE()), 20) AS 'ETA Completion Time',
    CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS 'Elapsed Minutes',
    CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0 / 60.0) AS 'Elapsed Hours',
    CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS 'ETA Minutes',
    CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0/ 60.0) AS 'ETA Hours',
    CONVERT(VARCHAR(1000), (
        SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
            WHEN r.statement_end_offset = -1
                THEN 1000
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2
            END)
        FROM sys.dm_exec_sql_text(sql_handle)
    )) AS 'T-SQL Statement'
FROM sys.dm_exec_requests r
WHERE command IN (
    'RESTORE DATABASE',
    'BACKUP LOG',
    'BACKUP DATABASE'
);

SQL

Sample output

Backup activity

SELECT 
    session_id AS SPID, 
    command, 
    a.text AS Query, 
    start_time, 
    percent_complete,
    DATEADD(second, estimated_completion_time/1000, GETDATE()) AS estimated_completion_time,
    CONVERT(char(8), DATEADD(second, DATEDIFF(second, start_time, DATEADD(second, estimated_completion_time/1000, GETDATE())), '1900-1-1'), 8) AS BackupTime,
    CONVERT(char(8), DATEADD(second, DATEDIFF(second, GETDATE(), DATEADD(second, estimated_completion_time/1000, GETDATE())), '1900-1-1'), 8) AS MinuteToComplete
FROM 
    sys.dm_exec_requests r 
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) a
WHERE 
    r.command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE');

JavaScript