August 2, 2012

Notes: SQL Scipt Checking REALTIME backup or restore status with time and percentage

Applicable for backup or restore scenario for SQL server instance. In some version of SQL server, upon executing a SQL backup or restore in management studio or tools you will not see the percentage or anything. Worst the backup file that you expect just exist with 0KB file.

Go to management studio and open a new query to execute below

USE DATABASE
SELECT a.name, b.total_elapsed_time / 60000 AS [running time], b.estimated_completion_time / 60000 AS [remaining], b.percent_complete as [% complete], (SELECT text FROM sys.dm_exec_sql_text(b.sql_handle)) AS command FROM master..sysdatabases a INNER JOIN sys.dm_exec_requests b ON a.dbid = b.database_id WHERE b.command LIKE '%BACKUP%' ORDER by b.percent_complete DESC, b.total_elapsed_time / 60000 DESC