SQL 2005: Using Data Management Views (DMV’s) to View Status of Database and Log Restores

SQL 2005 introduced some cool "data management views" (DMV’s) that let a DBA see what is happening on their database. Previously you had to use undocumented system tables and information schemas to get the info, and a lot of the info wasn’t even available. Well recently I was doing some backup/restore stuff on a database and wanted to know when it would finish. The first question I ask is – why isn’t this built into the SSMS view of a db, when it says "DatabaseName (Restoring)" why couldn’t they just add the % done at the end of something? Anyway’s – if you want to see what is restoring on your server and where it is at, go ahead and use this query:

use master
go

SELECT
    percent_complete AS ‘PctComplete’,
    start_time AS ‘StartTime’,
    command AS ‘Command’,
    b.name AS ‘DatabaseName’,
    DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
    (estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
    FROM sys.dm_exec_requests a
    INNER JOIN sys.databases b ON a.database_id = b.database_id
    WHERE command like ‘%restore%’
    AND estimated_completion_time > 0

I did notice that it says 100% complete but then the restore still takes about 20-30 seconds to finish, so be warned there. Also, if you are restoring a DB, the database name will be master (from what I have seen, it was a fresh restore). If you are just restoring logs, it will show you the database name of the database where the logs are restoring.

Take this and create a quick SSRS (Reporting Services) report. Or even better schedule this to run every 2 minutes or something and maybe once it sees some records, kick off a report, or make a cool little desktop app that has balloon popups and statuses, so as  DBA you can see what is going on with your restores. Maybe I will create that app one Saturday if am bored or something :)