SQL-Server: Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?

I found this sample script here that seems to be working pretty well:

SELECT r.session_id , r.command , CONVERT(NUMERIC(6,2), r.percent_complete) AS [Percent Complete] , CONVERT(VARCHAR(20), DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time] , CONVERT(NUMERIC(10,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min] , CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0) AS [ETA Min] , CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours] , CONVERT(VARCHAR(1000), (SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle) ) ) AS [SQL] FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE') 

If you know the sessionID then you can use the following:

SELECT * FROM sys.dm_exec_requests WHERE session_id = 62 

Or if you want to narrow it down:

SELECT command, percent_complete, start_time FROM sys.dm_exec_requests WHERE session_id = 62 

Here's a simple script that generally does the trick for me:

SELECT command, percent_complete,total_elapsed_time, estimated_completion_time, start_time FROM sys.dm_exec_requests WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE') 

Script to check the Backup and Restore progress in SQL Server:

Many times it happens that your backup (or restore) activity has been started by another Database Administrator or by a job, and you cannot use the GUI anything else to check the progress of that Backup / Restore.

By combining multiple commands, I have generated below script which can give us a summary of current backups and restores which are happening on the server.

select r.session_id, r.blocking_session_id, db_name(database_id) as [DatabaseName], r.command, [SQL_QUERY_TEXT] = Substring(Query.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(Query.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1), [SP_Name] =Coalesce(Quotename(Db_name(Query.dbid)) + N'.' + Quotename(Object_schema_name(Query.objectid, Query.dbid)) + N'.' + Quotename(Object_name(Query.objectid, Query.dbid)), ''), r.percent_complete, start_time, CONVERT(VARCHAR(20), DATEADD(ms, [estimated_completion_time], GETDATE()), 20) AS [ETA_COMPLETION_TIME], CONVERT(NUMERIC(6, 2), r.[total_elapsed_time] / 1000.0 / 60.0) AS [Elapsed_MIN], CONVERT(NUMERIC(6, 2), r.[estimated_completion_time] / 1000.0 / 60.0) AS [Remaning_ETA_MIN], CONVERT(NUMERIC(6, 2), r.[estimated_completion_time] / 1000.0 / 60.0/ 60.0) AS [ETA_Hours], wait_type, wait_time/1000 as Wait_Time_Sec, wait_resource from sys.dm_exec_requests r cross apply sys.fn_get_sql(r.sql_handle) as Query where r.session_id>50 and command IN ('RESTORE DATABASE','BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG') 

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') 

Try wih :

SELECT * FROM sys.dm_exec_requests where command like '%BACKUP%' SELECT command, percent_complete, start_time FROM sys.dm_exec_requests where command like '%BACKUP%' SELECT command, percent_complete,total_elapsed_time, estimated_completion_time, start_time FROM sys.dm_exec_requests WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE') 

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

Use STATS in the BACKUP command if it is just a script.

Inside code it is a bit more complicated. In ODBC for example, you set SQL_ATTR_ASYNC_ENABLE and then look for SQL_STILL_EXECUTING return code, and do some repeated calls of SQLExecDirect until you get a SQL_SUCCESS (or eqiv).

Use STATS option: http://msdn.microsoft.com/en-us/library/ms186865.aspx

I think the best way to find out how your restore or backup progress is by the following query:

USE[master] GO 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') GO 

The query above, identify the session by itself and perform a percentage progress every time you press F5 or Execute button on SSMS!

The query was performed by the guy who write this post

Add STATS=10 or STATS=1 in backup command.

BACKUP DATABASE [xxxxxx] TO DISK = N'E:\\Bachup_DB.bak' WITH NOFORMAT, NOINIT, NAME = N'xxxx-Complète Base de données Sauvegarde', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO. 

For anyone running SQL Server on RDS (AWS), there's a built-in procedure callable in the msdb database which provides comprehensive information for all backup and restore tasks:

exec msdb.dbo.rds_task_status; 

This will give a full rundown of each task, its configuration, details about execution (such as completed percentage and total duration), and a task_info column which is immensely helpful when trying to figure out what's wrong with a backup or restore.

I had a similar issue when working on Database restore operation on MS SQL Server 2012.

However, for my own scenario, I just needed to see the progress of the DATABASE RESTORE operation in the script window

All I had to do was add the STATS option to the script:

USE master; GO ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO RESTORE DATABASE mydb FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\my_db_21-08-2020.bak' WITH REPLACE, STATS = 10, RESTART, MOVE 'my_db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\my_db.mdf', MOVE 'my_db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb_log.ldf' GO ALTER DATABASE mydb SET MULTI_USER; GO 

And then I switched to the Messages tab of the Script window to see the progress of the DATABASE RESTORE operation:

If you want to get more information after the DATABASE RESTORE operation you can use this command suggested by eythort:

SELECT command, percent_complete, start_time FROM sys.dm_exec_requests where command = 'RESTORE DATABASE' 

That's all.

I hope this helps

To monitor the backup or restore progress completely separate from the session where the backup or restore was initiated. No third party tools required. Tested on Microsoft SQL Server 2012.

SELECT percent_complete, * FROM sys.dm_exec_requests WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' ) 

I am using sp_whoisactive, very informative an basically industry standard. it returns percent complete as well.

I love the script below for monitoring backups or anything in general going on (doesn't work for monitoring encrypt/decrypt). I copied it from someone smarter than me and just made the fairly obvious tweak to get the completion estimation time.

SELECT percent_complete,estimated_completion_time/(1000*60) AS EstimatedCompletionTimeInMinutes ,r.start_time [Start Time],session_ID [SPID], DB_NAME(database_id) [Database], SUBSTRING(t.text,(r.statement_start_offset/2)+1, CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 ELSE (r.statement_end_offset-r.statement_start_offset)/2+1 END) [Executing SQL], Status,command,wait_type,wait_time,wait_resource, last_wait_type FROM sys.dm_exec_requests r OUTER APPLY sys.dm_exec_sql_text(sql_handle) t WHERE session_id != @@SPID -- don't show this query AND session_id > 50 -- don't show system queries ORDER BY r.start_time 

you can run the following to get a detail reports on MS SQL for your restore progress-

exec msdb.dbo.rds_task_status;

simply run bkp_status on master db you will get backup status