By Richie Lee

Hello!

If you have backups running through jobs you may want to know the progress of the backup and where the backup media is. Script below returns this information for you.

Happy scripting!

SELECT A.NAME
	,B.TOTAL_ELAPSED_TIME / 60000 AS [Running Time]
	,B.ESTIMATED_COMPLETION_TIME / 60000 AS [Remaining]
	,B.PERCENT_COMPLETE AS [%]
	,(
		SELECT TEXT
		FROM sys.dm_exec_sql_text(B.SQL_HANDLE)
		) AS COMMAND
FROM MASTER..SYSDATABASES A
	,sys.dm_exec_requests B
WHERE A.DBID = B.DATABASE_ID
	AND B.COMMAND LIKE '%BACKUP%'
ORDER BY percent_complete DESC
	,B.TOTAL_ELAPSED_TIME / 60000 DESC