As a database administrator (DBA), it is crucial to have a set of scripts that can help in troubleshooting and monitoring various activities in SQL Server. One common task is to check the progress of backup or restore operations happening on the server. In this article, we will discuss a script that can provide a summary of current backups and restores in SQL Server.

The Script

Here is a script that combines various commands to give us the status of running backup and restore operations:

SELECT r.session_id AS [Session_Id]
	,r.command AS [Command]
	,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
	,GETDATE() AS [Current Time]
	,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
	,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
	,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
	,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated 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) 'Statement text'
			FROM sys.dm_exec_sql_text(sql_handle)
			))
FROM sys.dm_exec_requests r
WHERE command LIKE 'RESTORE%'
OR command LIKE 'BACKUP%'
	

This script retrieves information from the sys.dm_exec_requests and sys.dm_exec_sql_text system views. It provides details such as session ID, command, percentage complete, current time, estimated completion time, elapsed time, and the statement being executed.

Real-World Scenario

Recently, while performing a performance tuning exercise for a customer, we encountered a situation where one of their drives had high I/O activity. By running the above script, we quickly identified that a backup operation was in progress on the system. This information helped us pinpoint the cause of the increased I/O and take appropriate actions to optimize the backup process.

Your Contributions

Do you have any similar interesting scripts that you find useful in your day-to-day SQL Server administration tasks? We encourage you to share your scripts and experiences in the comments section below. Your contributions can help fellow DBAs and SQL Server enthusiasts enhance their troubleshooting and monitoring capabilities.

Stay tuned for more SQL Server tips and tricks!