Wednesday 16 May 2012

check backup and restore progress in SQL Server


The below script may be helpful in checking the status of huge database activity. The below script will work for any backup or restore that is currently running regardless of which method was used to run the backup or restore.

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

percentage of database activity completion and estimated time taken to complete.


0 comments: