Tuesday, June 14, 2011

SQL Server – Restore Database – Time Estimate or Progress / How can we query how much time SQL database restore will takes thru T-SQL query?

While database restore is in progress or database is in restoring mode, you can check the estimated restore percentage complete , estimated completion time, start time with below DMV.

SELECT  sp.name ,
        percent_complete AS 'PERCENTAGE COMPLETE',
        DATEADD(second, estimated_completion_time / 1000, GETDATE()) AS 'Est Completion Time' ,
        GETDATE() AS 'Current Time' ,
        DATEDIFF(minute, start_time, GETDATE()) AS running ,
        estimated_completion_time / 1000 / 60 AS 'Completion Time' ,
        start_time ,
FROM    sys.dm_exec_requests req
        INNER JOIN sys.sysdatabases sp ON sp.dbid = req.database_id
WHERE   req.command LIKE '%RESTORE%'