Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?

Question

When I backup or restore a database using MS SQL Server Management Studio, I get a visual indication of how far the process has progressed, and thus how much longer I still need to wait for it to finish. If I kick of the backup or restore with a script, is there a way to monitor the progress, or do I just sit back and wait for it to finish (hoping that nothing has gone wrong?)

Edited: My need is specifically to be able to monitor the backup or restore progress completely separate from the session where the backup or restore was initiated.

Answer 1

Use STATS option: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Answer 2

I found this sample script here that seems to be working pretty well:

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Answer 3

Use STATS in the BACKUP command if it is just a script.

Inside code it is a bit more complicated. In ODBC for example, you set SQL_ATTR_ASYNC_ENABLE and then look for SQL_STILL_EXECUTING return code, and do some repeated calls of SQLExecDirect until you get a SQL_SUCCESS (or eqiv).

Answer 4

Yes. If you have installed sp_who2k5 into your master database, you can simply run:

sp_who2k5 1,1

The resultset will include all the active transactions. The currently running backup(s) will contain the string "BACKUP" in the requestCommand field. The aptly named percentComplete field will give you the progress of the backup.

Note: sp_who2k5 should be a part of everyone's toolkit, it does a lot more than just this.