Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

---

Scenario:

We often get database backup and restore tasks in SQL server while deployment of something or for testing purpose from developer.

In this case, they may ask us to provide them update on backup and restore progress.

Here, we have script available to check backup and restore completion in percentage along with estimated time of completion which should be very helpful to plan accordingly.

There are multiple ways to check percentage of progress in SQL server i.e. SSMS window shows the progress if backup or restore are done through GUI but here we will check the progress in percentage using DMVs. in SQL server.

 

---

Using DMVs

If the backup or restore is running from a SQL Agent job or maybe someone kicked off the process from another machine, you can use DMV – sys.dm_exec_requests to find the progress.

You can run this script, here we can see the percent complete and estimated completion time. This script will work for any backup or restore that is currently running regardless of what method was used to run the backup or restore.

---

SELECT session_id as SPID, command, sh.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) sh
WHERE req.command in ('BACKUP DATABASE','RESTORE DATABASE')

Or, we can use below one also:-

SELECT session_id as SPID, command, sh.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) sh
WHERE percent_complete>0

Happy Learning!

---

Do not reveal what you have thought upon doing, but by wise council keep it secret being determined to carry it into execution.

Chanakya