Bulk Restores - Which Database is Restoring Now?

As I discussed in an earlier post, I’ve been looking at backup & restore operations and the length of time they take to complete.
It has, with further use, come to my attention that the final query presented doesn’t necessarily work when restoring databases.

Reminder

As you should know, one of the things a DBA should do is test backups regularly. Frequently. A non-restored backup is an untested backup, and all that.

So what I’ve done is wrap up the whole restore process into a script that loops through all the backup files and restores them one by one – full backups, differentials, and logs. The way that this particular script runs, though, means that all the full backups are processed first, then all diffs (if appropriate), and finally all tlogs, one database at a time.

So, occasionally, I want to know how far I’ve got with restoring a particular database backup.

Out with the old…

SELECT
    start_time,
    percent_complete,
    estimated_completion_time,
    DATEADD(ms, estimated_completion_time, GETDATE()) AS ETA,
    GETDATE() AS Current_DateTime,
    command,
    DB_NAME(database_id) AS Database_Name
FROM
    sys.dm_exec_requests
WHERE
    command LIKE 'RESTORE%'

Here, we can see that the output looks like it’s restoring the master database, which it isn’t. Can you imagine a server with a master database that requires that run-time to restore? Because I don’t want to…

…And in with the new

SELECT  start_time ,
        percent_complete ,
        estimated_completion_time ,
        DATEADD(ms, estimated_completion_time, GETDATE()) AS ETA ,
        GETDATE() AS Current_DateTime ,
        command ,
        CASE WHEN command LIKE 'RESTORE%'
             THEN SUBSTRING(TEXT, CHARINDEX('[', TEXT, 15) + 1,
                            CHARINDEX(']', TEXT, 15) - 
                                 CHARINDEX('[', TEXT, 15) - 1)
             ELSE TEXT
        END
FROM    sys.dm_exec_requests r
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE   command LIKE 'BACKUP%'
        OR command LIKE 'RESTORE%'

And now we can see that we are getting the correct database name displayed.

And in SQL Server 2000

However, this new query doesn’t work for the odd SQL2000 instance that is still kicking around. For them, we have a less helpful query that doesn’t show start/end dates, but does at least show which database is being worked on.

DECLARE @DBCC VARCHAR(2000)
DECLARE @SPIDs TABLE ( spid INT )
INSERT  INTO @SPIDs
        ( spid
        )
        SELECT DISTINCT
                spid
        FROM    sysprocesses
        WHERE   cmd LIKE 'Restore%'
                OR cmd LIKE 'Backup%'
SELECT  @DBCC = ''
SELECT  @DBCC = @DBCC + '
DBCC INPUTBUFFER(' + CONVERT(VARCHAR(5), spid) + ')'
FROM    @SPIDs
EXEC (@DBCC)

Not elegant, but it does the job:

even for T-Log restores:

About these ads