October 6, 2014 at 7:04 pm
Hi SQL Masters,
Greetings!
Just like to raise a question regarding Restore Database. On the Object Explorer, the database is marked as Restoring, however, when i run the script below, it returns null/Blank. Does anyone experienced this weird case?
SELECT session_id as SPID, command, aa.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) aa
WHERE r.command in('BACKUP DATABASE','RESTORE DATABASE')
October 6, 2014 at 8:57 pm
The "command" column actually contains the full text of the restore command. You will need to use something like
SELECT session_id as SPID, command, aa.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) aa
WHERE r.command LIKE '%BACKUP DATABASE%'
OR r.command LIKE '%RESTORE DATABASE%'
Even the above will not always work because it does assume that there is a single space prior to the word "DATABASE" in the command.
October 7, 2014 at 12:27 am
Just a sidenote: if the restore command is executed with the option "WITH NORECOVERY" the database stays in the restoring state. You won't find an corresponding active RESTORE command, because it will already be finished...
October 15, 2014 at 10:13 pm
Hello Guys,
Thanks for the replies... i appreciate it much!:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply