Display Current Backup/Restore Progress Using DMVs   1 comment

Here is a script gleaned from an article at http://www.mssqltips.com/tip.asp?tip=2343, with some minor modifications of mine, which very nicely allows you to monitor SQL backup or restore progress, including estimated completion times, based on data from system DMVs. This script works for SQL 2005 and above.

What is nice also is that it not only reports on native SQL backups and restores, but also third-party backups and restores performed by tools such as Quest SQL LiteSpeed or the Simpana CommVault SQL backup agent.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

--Display current backup/restore progress using DMVs.
--SQL 2005 and above
--From http://www.mssqltips.com/tip.asp?tip=2343

USE master

SELECT
	session_id as SPID,
	CONVERT(VARCHAR(50),start_time,100) AS start_time,
	percent_complete,
	CONVERT(VARCHAR(50),dateadd(second,estimated_completion_time/1000, getdate()),100) as estimated_completion_time,
	command, a.text AS Query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'

Posted June 20, 2011 by Norm Enger in Microsoft SQL Server

Advertisements

Posted June 20, 2011 by Norm Enger in Microsoft SQL Server

One response to “Display Current Backup/Restore Progress Using DMVs

Subscribe to comments with RSS.

  1. Reblogged this on Proxima's IT Admin Corner.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: