SQLDBA- verify the backup percentage on prem sql server

 SELECT

    db.name AS DatabaseName,

    r.command,

    r.status,

    r.percent_complete,

    r.start_time,

    r.estimated_completion_time / 1000 / 60 AS EstimatedCompletion_Minutes,

    r.total_elapsed_time / 1000 / 60 AS ElapsedTime_Minutes

FROM 

    sys.dm_exec_requests r

JOIN 

    sys.databases db ON r.database_id = db.database_id

WHERE 

    r.command IN ('BACKUP DATABASE', 'BACKUP LOG');

There  are other  ways also.

/*==================================================================

Script: Monitor Backup Restore Dbcc.sql

Description: This script will display estimated completion times

and ETAs of Backup, Restore and DBCC operations.

Date created: 13.09.2018 (Dominic Wirth)

Last change: -

Script Version: 1.0

SQL Version: SQL Server 2008 or higher

====================================================================*/

SELECT Req.percent_complete AS PercentComplete

,CONVERT(NUMERIC(6,2),Req.estimated_completion_time/1000.0/60.0) AS MinutesUntilFinish

,DB_NAME(Req.database_id) AS DbName,

Req.session_id AS SPID, Txt.text AS Query,

Req.command AS SubQuery,

Req.start_time AS StartTime

,(CASE WHEN Req.estimated_completion_time < 1

THEN NULL

ELSE DATEADD(SECOND, Req.estimated_completion_time / 1000, GETDATE())

END) AS EstimatedFinishDate

,Req.[status] AS QueryState, Req.wait_type AS BlockingType,

Req.blocking_session_id AS BlockingSPID

FROM sys.dm_exec_requests AS Req

CROSS APPLY sys.dm_exec_sql_text(Req.[sql_handle]) AS Txt

WHERE Req.command IN ('BACKUP DATABASE','RESTORE DATABASE') OR Req.command LIKE 'DBCC%';










-- Backup percentage with login name

SELECT r.session_id, r.command, -- BACKUP DATABASE / LOG / RESTORE r.status, r.percent_complete, r.start_time, r.estimated_completion_time / 1000 / 60 AS est_completion_minutes, DB_NAME(r.database_id) AS database_name, s.login_name, -- SQL/Windows login executing it s.original_login_name, -- original login before EXECUTE AS / IMPERSONATION s.host_name, -- client machine s.program_name, -- application (e.g., SQLAgent, SSMS, PowerShell) c.client_net_address, -- client IP r.blocking_session_id FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id LEFT JOIN sys.dm_exec_connections AS c ON r.session_id = c.session_id WHERE r.command LIKE '%BACKUP%' -- change to '%RESTORE%' for restores ORDER BY r.start_time DESC;







-- Restoring percenatge




SELECT
session_id,
command,
percent_complete,
start_time,
total_elapsed_time / 1000.0 / 60.0 AS elapsed_minutes,
estimated_completion_time / 1000.0 / 60.0 AS estimated_remaining_minutes
FROM
sys.dm_exec_requests
WHERE
command IN ('RESTORE DATABASE', 'RESTORE LOG');