I was recently doing some work with a backup verification routine that is meant to run on various versions of SQL Server and came across some variations in the way the RESTORE VERIFYONLY command is reported by sys.dm_exec_requests between SQL Server 2005, 2008 R2, and 2012. I haven’t seen this mentioned anywhere, so I thought I’d document it here. This is useful information to have if you are doing any type of programming based on the output of sys.dm_exec_requests while using the RESTORE VERIFYONLY command.

The table below notes the differences. Note the change of field length in SQL 2008 / 2008 R2, which results in what appears to be the truncation of the command text. I have not run this test on SQL Server 2014.

The command run was:

RESTORE VERIFYONLY
FROM DISK = N'<backup path and file here>'

And the code to view sys.dm_exec_requests while the above is running is:

SELECT	session_id,
		status,
		percent_complete,
		command,
		wait_type,
		wait_time,
		CAST(estimated_completion_time AS DECIMAL(20,3))/1000.0/60.0 AS EstimatedCompletionTimeInMinutes,
		last_wait_type
FROM	sys.dm_exec_requests
WHERE session_id >50
order by percent_complete desc

SQL Server Version	Sys.dm_exec_requests.Command data type	Command text reported
2005	Nvarchar(32)	RESTORE VERIFYON
2008 / 2008 R2	Nvarchar(16)	RESTORE HEADERON
2012	Nvarchar(32)	RESTORE HEADERONLY