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