IT Giant

In SQL2000, we can­’t find out the backup pro­gress unless we give the stats para­met­ers in the backup state­ment. But in SQL 2005/​2008, with DMVs we are cap­able of find out and now we can answer most of user ques­tions like “When the backup will fin­ish? How far we’ve gone through the whole backup?”.

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%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc