Imagine a scenario: You have a very busy non-production environment hosting dozens of databases. Lots of developers are actively using the server. They are backing up the databases and restoring them from production as well, as and when needed.
As a DBA, wouldn’t it be nice to have a script that simply acts as a dashboard and shows you all the currently running BACKUP and RESTORE commands on your server, along with details like the backup/restore start time, elapsed time, what percentage of the operation is complete, the ETA, who is running it, from where etc.?
Another scenario: You are working on a server migration, which involves moving lots of databases from source server to the target server. There are lots of backups and restores involved. Wouldn’t it be useful to have a handy script, that showed you the status of all the backup commands on the source server, and also the status of all the restore commands on the target server?
If you regularly find yourself in such scenarios, then you’re in the right place. Here’s a script that does just that, and helps you keep track of the progress of all the current backup and restore operations. The output includes the below commands:
PercentComplete - Completion percentage of the command - (example: 91.02%)
Command - Type of operatino (example: BACKUP DATABASE or RESTORE DATABASE)
StartTime - Time the backup or restore operation started
TimeNow - Current server time
ETA - Expected time of completion
Elapsed_time - Time the current operation has been running for (example: 00h 10m 25s)
Remaining_time - Time remaining for the current operation (example: 00h 30m 15s)
SPID - Session ID of the connectino that's running the command
LoginName - The login name that initiated the command
HostName - Client host name from where the command has been initiated
Program - Name of the application that initiated the command
SQLStatement - The actual BACKUP or RESTORE command--Written by Vyas Kondreddi
--https://dutydba.com
SET NOCOUNT ON
GO
--CTE to gather the basic command information
WITH BackupsAndRestores
AS
(
SELECT
[session_id] AS SPID,
CONVERT(varchar(20), start_time, 120) AS StartTime,
CONVERT(varchar(20), CURRENT_TIMESTAMP, 120) AS TimeNow,
RIGHT('00000' + CAST(CAST(percent_complete AS numeric(5, 2)) AS varchar(6)), 5) + '%' AS PercentComplete,
total_elapsed_time/1000 AS ElapsedSec,
CAST((100 * DATEDIFF(ss, start_time, CURRENT_TIMESTAMP))/NULLIF(percent_complete,0) AS bigint) -
total_elapsed_time/1000 AS RemainingSec,
[sql_handle] AS SQLHandle,
Command,
statement_start_offset,
statement_end_offset
FROM
sys.dm_exec_requests
WHERE
Command IN ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
)
SELECT
br.PercentComplete,
br.Command,
br.StartTime,
br.TimeNow,
CONVERT(varchar(20), DATEADD(ss, br.RemainingSec, br.TimeNow), 120) AS ETA,
--Display the elapsed time in a readable, user friendly format
RIGHT('00' + CAST(br.ElapsedSec/3600 AS varchar(2)), 2) + 'h ' +
RIGHT('00' + CAST((br.ElapsedSec%3600)/60 AS varchar(2)), 2) + 'm ' +
RIGHT('00' + CAST((br.ElapsedSec%3600)%60 AS varchar(2)), 2) + 's' AS Elapsed_time,
--Display the remaining time in a readable, user friendly format
RIGHT('00' + CAST(br.RemainingSec/3600 AS varchar(2)), 2) + 'h ' +
RIGHT('00' + CAST((br.RemainingSec%3600)/60 AS varchar(2)), 2) + 'm ' +
RIGHT('00' + CAST((br.RemainingSec%3600)%60 AS varchar(2)), 2) + 's' AS Remaining_time,
br.SPID,
es.login_name AS LoginName,
es.[host_name] AS HostName,
es.[program_name] AS Program,
--Extract the actual BACKUP/RESTORE command
SUBSTRING(sh.[text],
(
br.statement_start_offset/2) + 1,
(
(
CASE br.statement_end_offset
WHEN -1
THEN DATALENGTH(sh.[text])
ELSE
br.statement_end_offset
END - br.statement_start_offset
)
/2
) +1
) AS SQLStatement
FROM
BackupsAndRestores AS br
OUTER APPLY
sys.dm_exec_sql_text(br.SQLHandle) AS sh
LEFT JOIN
sys.dm_exec_sessions AS es
ON es.[session_id] = br.SPID
ORDER BY br.StartTime
Here’s a screenshot showing sample output:
See Also:
How to find when, from where, and to what point every SQL Server Database was last restored on your server?
How to check the last backup date for every database on your SQL Server?
