Query to Find time remaining to complete database backup or restore in SQL 2005 | SansSQL

Tuesday, November 18, 2008

Query to Find time remaining to complete database backup or restore in SQL 2005

USE master
GO

SELECT
          Percent_Complete,
          Start_Time ,
          Command,
          b.Name AS DatabaseName, --Sometimes this will be "Main" as the database will not be accesiable.
          DATEADD(ms,estimated_completion_time,GETDATE()) AS RemainTime,
          (estimated_completion_time/1000/60) AS MinutesToFinish
FROM sys.dm_exec_requests a
          INNER JOIN sys.databases b
          ON a.database_id = b.database_id
WHERE
          Command like '%Restore%'
          OR Command like '%Backup%'
          AND Estimated_Completion_Time > 0

6 comments:

prasad yangamuni said...

Hey This script is beautiful.. i tried alot to findout this script.. but finally i got here..

Great work sandesh.. really appreciate it..

Anonymous said...

Awsome. It works perfectl

Sen.

Anonymous said...

Perfect !!!

Krishna said...

Query to Find time remaining to complete Rebuild Index for entire DB.

Krishna said...

Hi, we can rewrite the command for Checking Backup and Restore estimated time.
select percent_complete ,estimated_completion_time/60000 RemMin,* from sys.dm_exec_requests where command ='backup database'

Anvesh Patel said...

Nice Article !

Really this will help to people of SQL Server Community.
I have also prepared small note on this, find estimated finish time of the restore SQL Server database.

http://www.dbrnd.com/2015/11/sql-server-script-to-find-estimated-finish-time-of-the-restore-database-2/

Post a Comment