Friday, August 8, 2014

Script to find out the Estimated completed time for Backup/restore operations

By using below script you can find out the Estimated completed time for Backup/restore operations

SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND (B.COMMAND LIKE '%BACKUP%' OR B.COMMAND LIKE '%RESTORE%')
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc