How to find transaction log backup history using query?

All Replies

• Monday, January 28, 2013 1:08 PM

   

   
  

  1
  Select * from msdb.dbo.backupset where type ='L' This query will return all log backups information, if you want any particular database try this Select * from msdb.dbo.backupset where type ='L' and database_name = <<your database name>>

  ---

  Hope it Helps!!

  
  
  

  • Edited by Stan210 Monday, January 28, 2013 1:08 PM
  • Edited by Stan210 Monday, January 28, 2013 1:10 PM
  • Proposed As Answer by Murali P BMicrosoft Employee Monday, January 28, 2013 1:13 PM
  • Marked As Answer by Itrit Monday, January 28, 2013 3:54 PM
  •  
  • Reply
  • Quote

   
• Monday, January 28, 2013 1:20 PM

   

   
  

  1

  Hi,

  You can use,

  SELECT      bs.backup_start_date, bs.backup_finish_date, * 
  FROM        msdb..backupfile bf
  JOIN        msdb..backupset bs ON bf.backup_set_id = bs.backup_set_id
  WHERE       bf.File_Type = 'L'

  or using

  select database_name, max(backup_finish_date) 
  from msdb..backupset
  where type = 'L'
  group by database_name

  
  

  Following forum thread might be helpful also

  http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f0c32d8e-e2b8-4190-b2dd-273a8cbb7b75

  ---

  
  Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"
  
  Waqas
  
  MS(SPM), MS(E&F),M.Engg, MCP, MCT, MCTS, MCITP, MCSE, MCPD, MCSD, MCDBA , Author
  Twitter: @waqas8777
  Linked In: http://www.linkedin.com/in/waqasm1

  • Proposed As Answer by Waqas M Monday, January 28, 2013 1:22 PM
  • Marked As Answer by Itrit Monday, January 28, 2013 3:53 PM
  •  
  • Reply
  • Quote