Backup or Restore progess
This procedure provides the information like when the backup/Restore for a particular database is started and what is the average time it will take to complete and from how long it is running and by when it will be completed. With this information we can wait for the average shown time for the completion of the task and we can take up other challenges instead of waiting on this for long time.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[prDBA_Check_BackuporRestoreProgress] Script Date: 02/19/2010 05:04:17 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[prDBA_Check_BackuporRestoreProgress]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[prDBA_Check_BackuporRestoreProgress]
GO
/****** Object: StoredProcedure [dbo].[prDBA_Check_BackuporRestoreProgress] Script Date: 02/19/2010 05:03:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[prDBA_Check_BackuporRestoreProgress]
as
BEGIN
SELECT @@servername, command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
END 
