Percent_Complete – detailed output

Percent_Complete is readily available in the sys.dm_exec_requests table. I have enhanced it used if you want to track events based on time remaining and duration

SELECT 
percent_complete
,command
,session_id
,start_time 
,getdate() as 'Time Now' 
,DATEDIFF(mi, start_time, getdate()) as 'Duration (Mins)'
,    (((100/percent_complete)*DATEDIFF(mi, start_time, getdate()))
    -DATEDIFF(mi, start_time, getdate()))/60 as 'Hours Remaining' 
,t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE percent_complete > 0

Progress of a Backup or Restore in SQL 2000?

In SQL 2005 onwards, you can use DMV’s to monitor percentage completed of certain operations (https://dbamohsin.wordpress.com/2011/02/08/dmvs-sys-dm_exec_requests-and-percent_complete/)

Is this possible in SQL 2000 apart from using the STATS flag on the BACKUP or RESTORE syntax…

Yes…

Use the following command against the SPID running the backup or Restore to see buffer information

DBCC OUTPUTBUFFER(spid)

Output Buffer                                                                
—————————————————————————–

00000000   04 00 00 5d 00 38 06 00 79 01 00 00 00 ab 44 00   …].8..y….«D.

00000010   8b 0c 00 00 01 00 14 00 33 00 30 00 20 00 70 00   ‹…….3.0. .p.

00000020   65 00 72 00 63 00 65 00 6e 00 74 00 20 00 72 00   e.r.c.e.n.t. .r.

00000030   65 00 73 00 74 00 6f 00 72 00 65 00 64 00 2e 00   e.s.t.o.r.e.d…