[SQL SERVER] – CHECK RESTORE / BACKUP PROGRESS

Having to temporarily wear the DBA hat for a couple of days, I found myself backing up and restoring lots of databases, across different environments.

When you script the backup/restore, you can choose to output the progress, but if you run the backup/restore inside a SQL agent job, you won’t have any feedback until the job has finished.

But fear not, you can have some insights on the progress using the following query

SELECT
   e.session_id,
   t.text Command,
   e.start_time,
   e.percent_complete
FROM sys.dm_exec_requests e
CROSS APPLY sys.dm_exec_sql_text(e.sql_handle) t
WHERE e.command = 'BACKUP DATABASE' -- 'RESTORE DATABASE'

And there you go!

Some databases can remain on 100% completion for a while, just be patient.