In SQL2000, we can’t find out the backup progress unless we give the stats parameters in the backup statement. But in SQL 2005/2008, with DMVs we are capable of find out and now we can answer most of user questions like “When the backup will finish? 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
No Comments