Determining if backup is running

• Ken Schwartz

  SSC Veteran

  Points: 231

  More actions

  December 17, 2008 at 9:40 am

  #195227

  Hi,

  I am looking for a way, via code, to determine if a backup process is currently running.

  I know that msdb.backupset contains backup_startDate and backup_endDate fields, but it seems like a record is only entered into that table when a backup has completed. (I was hoping I would see something like a value for the startDate but NULL for the endDate...no such luck.)

  Does anyone have any suggestions?

  thanks very much,

  Ken

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  December 17, 2008 at 10:08 am

  #913999

  you could query the sysprocesses table, if there were any backups running the column 'cmd' would have an entry of 'backup database'

  ---------------------------------------------------------------------

• Jorge Novo-@ETLDEVDBA

  Ten Centuries

  Points: 1265

  More actions

  December 17, 2008 at 1:22 pm

  #914129

  Hey I post the below code but here it is :

  enjoy

  use master

  go

  SELECT

  percent_complete,

  start_time ,

  command,

  b.name AS DatabaseName, --Most of the time will said Main but this is because db is not accesible

  DATEADD(ms,estimated_completion_time,GETDATE()) AS RemainTime,

  (estimated_completion_time/1000/60) AS MinutesToFinish

  FROM sys.dm_exec_requests a

  INNER JOIN sys.databases b

  ON a.database_id = b.database_id

  WHERE command like '%restore%'

  or command like '%Backup%'

  AND estimated_completion_time > 0