Estimated Time for Backup / Restore

Estimated Time for Backup / Restore

This script can be used to find the estimated time of backup and restore that is on progress in your SQL server. This script is applicable for SQL server 2005 and above.

Script

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Sample Output

session_id command Percent Complete ETA Completion Time Elapsed Min ETA Min ETA Hours
52 BACKUP DATABASE 95.76 2008-02-08 08:09:48 0.16 0.01 0.00 Backup database AdventureWorks to disk=’c:adw.bak’

Leave a Reply

7 responses to “Estimated Time for Backup / Restore”

  1. Worked fast and perfectly. Note: Do not substitute your DB names within the section: IN (‘RESTORE DATABASE’,’BACKUP DATABASE’). These literals are actually the correct values to use.

  2. Very useful query. I do have an observation, however. When I do a Restore Files and Filegroups (as opposed to Restore Database), there is no ETA or % complete data returned. Does this data not exist for this type of restore?

Leave a Reply

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Batch Script to Deploy Multiple SQL files (Version 2)
I took very long break to keep the site up to date with my learning. Few years back I wrote an article to deploy multiple scripts using batch fil...
Provisioning Azure Cosmos DB using Powershell
Azure Cosmos database is one of the NoSQL database that is available in Microsoft Azure cloud platform. Azure Cosmos db is getting more popular c...
July 2017 CSSUG Meet
We are back in action for this month. Thanks to all the participants who visited the last event. We are pleased to update you all that this mont...
powered by RelatedPosts