Get backup status in SQL Server 2005 & 2008

Performing database backups are one of the most frequently performed tasks by any DBA. Most of the times the maintenance window that the DBA team gets to perform database maintenance activities is usually less. More often that not, the database maintenance tasks run beyond the stipulated window and the Application Team starts complaining about this.

Even in our environment, usually the Application Team comes back to stating “Our critical jobs are about to start in the next few minutes. We see that one database backup process is utilizing most of the resources. Since it may affect our jobs, can you please KILL it?

For them it is the backup job which runs everyday. But only the DBA would know how precious a database backup is. I don’t recollect how many times that extra backup helped me out of tough situations or gave me the confidence to go ahead with some risky proposition.

When someone asks how long will that backup job runs or how much work it has already done, the best option in SQL Server 2000 was to check the output given by the stats parameter in the Backup script. But if someone forgot to include the stats parameter in the backup script, there is no way to check the backup completion status in SQL Server 2000.

But starting SQL Server 2005, this behavior has changed. One of the many wonderful DMVs to be included in SQL Server 2005 isĀ  sys.dm_exec_requests. With this DMV the status, percentage completed etc. of the Backup process (for that matter any SQL command) can be easily tracked. Here is the script which does exactly the same.

SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM 
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%' 
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc

I know that most of us are already using some or the other version of this script for a very long time now. Since this script recently helped David Rummel to track the backup of a large database, thought this might help someone else somewhere.

3 thoughts on “Get backup status in SQL Server 2005 & 2008

  1. Pingback: 100 percent restored, what else is going on? | SQL Server DBA Diaries of Pradeep Adiga

  2. Pingback: Restores stuck at 100% | howdoisql

Comments are closed.