A Frustration...

We all love this script, right?

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(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 [ETA MIN],

CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(100),(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)))

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

 

 
We all love being able to track backup and restore progress on a database, to be able to give our customers a general idea on when the database will finish restoring. When the current log which is being restored will finish, and we'll be caught up on log shipping. When the backup will (please, oh please) be done?

So, has anyone come up against:

Msg 927, Level 14, State 2, Line 1

Database 'dbname' cannot be opened. It is in the middle of a restore.

 

Well, yes, there is a restore running. This is why I am running the query in the first place...and, although a couple of people seem to have come up with the issue: does anyone know why this is the case? The database in question isn't encrypted...

 

Yours, stumped....