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....