By David Wiseman (Administrator)published 12 Sep 2008
My Rating:
Vote
Rating:
Not Rated
Views:27104

T-SQL Backup/Restore Progress

Overview

Backup and restore progress is typically monitored using a "STATS=percentage" argument in the backup/restore script.  The "messages" window in Management Studio can be used to check on progress or progress will be displayed in the GUI.  This doesn't allow you to monitor the progress of a backup that was run from SQL Server Agent or a backup/restore that was run by another administrator. 

The script in this article can be used to check on the progress of backup and restore operations.  It provides additional information such as the time the backup/restore started, the running time, estimated time to completion and an estimated completion time.  The script can be run at any time by any DBA to check on the progress of backup and restore operations.

Note: The script uses a dynamic management view, which is new to SQL Server 2005.  The script should also work on SQL Server 2008 but it won't be compatible with SQL Server 2000 or previous versions.

T-SQL Script

SELECT 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')
 

backup/restore progress script output




  Post Comment
Order By:  
User Comments
      
Be the first to post a comment!