Script that identifies the Percent Complete of a Restore, etc

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  December 5, 2014 at 9:12 am

  #301736

  I had a script that identifies the Percent Complete of a Restore, etc.

  I can not find it.

  Any help would be greatly appreciated.

  Thanks.

  Kent

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  December 5, 2014 at 9:29 am

  #1763006

  Adam Machanic's sp_whoisactive returns it.

  You can get it from sys.dm_exec_requests as well.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• sqlsurfing

  SSCarpal Tunnel

  Points: 4883

  More actions

  December 5, 2014 at 11:25 am

  #1763046

  Welsh Corgi (12/5/2014)

  ---

  I had a script that identifies the Percent Complete of a Restore, etc.

  I can not find it.

  Any help would be greatly appreciated.

  Thanks.

  Kent

  I got this from some where and saved it in my scripts folder hope it works for you

  -- ESTIMATED COMPLETION TIME SCRIPTS/RESTORE/BACKUP

  SELECT

  d.session_id, d.PERCENT_COMPLETE AS [%Complete],

  d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,

  d.ESTIMATED_COMPLETION_TIME/60000 AS TimeRemainingMin,

  d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,

  d.ESTIMATED_COMPLETION_TIME*0.00000024 AS TimeRemainingHours,

  s.text AS Command

  FROM sys.dm_exec_requests d

  CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s

  WHERE d.COMMAND LIKE 'Backup%'

  ORDER BY 2 desc, 3 DESC

  --------------------------------------------------
  ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

• DeWayne_McCallie

  Hall of Fame

  Points: 3617

  More actions

  December 5, 2014 at 1:25 pm

  #1763062

  --Display current backup/restore progress using DMVs.

  --SQL 2005 and above

  --From http://www.mssqltips.com/tip.asp?tip=2343

  USE master

  SELECT session_id AS SPID ,

  CONVERT(VARCHAR(50), start_time, 100) AS start_time ,

  percent_complete ,

  CONVERT(VARCHAR(50), DATEADD(SECOND, estimated_completion_time / 1000,

  GETDATE()), 100) AS estimated_completion_time ,

  command ,

  a.text AS Query

  FROM sys.dm_exec_requests r

  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a

  WHERE r.command LIKE 'BACKUP%'

  OR r.command LIKE 'RESTORE%'