[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.

Leave a Reply

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

CAPTCHA Image

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>