19 June 2009 12:59 tonyrogerson

Determining Backup or Restore Percent Complete in SQL Server 2008 and SQL Server 2005

If you want to see how far along your restore or backup is then you can do this... 

select percent_complete
from sys.dm_exec_requests
where session_id = 53

This is in addition to the STATS = n option on the BACKUP/RESTORE commands.

 

Filed under: ,

Comments

# re: Determining Backup or Restore Percent Complete in SQL Server 2008 and SQL Server 2005

22 July 2009 13:21 by Uri Dimant

Hi Tony

I hope you are doing well.

This is also true for those operations

DBCC CHECKDB

DBCC SHRINKDATABASE

DBCC SHRINKFILE

ROLLBACK

--Index Defrag

SELECT   r.percent_complete,

  estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP),  

t.[text]FROM   sys.dm_exec_requests AS r CROSS APPLY  

sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE   r.session_id = <SPID>;