Database Backup or Restore Progress

As a DBA, we normally do backup and restore the database in SQL server as a day to day activities or tickets.

Below Script will help you to find the status of  Backup (FULL,DIFF or Tlog) and Restore(database,log ,restore headeron and restore verifyon)

It will give you the estimated time to complete the operation (backup or restore).

select

d.name as [Database Name],Percent_complete, dateadd(second,estimated_completion_time/1000,
getdate()) as EstimatedCompletionTime, Getdate() as Current_Time_now, CAST((datediff(minute,
start_time, getdate())) as varchar(50)) +' ' + 'Minutes' as CMD_Running_From, CAST((estimated_completion_time/1000/60) as varchar(50)) +' ' + 'Minutes' as Will_Complete_in,
start_time as CMD_Start_Time, command As Running_Command
from sys.dm_exec_requests req
inner join sys.sysdatabases d on d.dbid = req.database_id
where req.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','RESTORE HEADERON','RESTORE VERIFYON')