how to find Last restored log backup in Log shipping

• Mani-584606

  SSChampion

  Points: 11788

  More actions

  November 29, 2009 at 3:48 pm

  #214190

  Hi,

  I have SQL Server 2005 and configured log shipping. I'm testing the failover and role switch scenarios. I looking the error log for the last restored log backup on secondary.

  Is there any script to find the last restored log backup on secondary?

  thanks

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  November 29, 2009 at 4:58 pm

  #1085200

  select last_restored_file from msdb.dbo.log_shipping_monitor_secondary where secondary_database = 'yourdb'

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

• sejal p gudhka

  SSCrazy

  Points: 2102

  More actions

  November 30, 2009 at 2:08 am

  #1085278

  you can also verify using job history.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  November 30, 2009 at 10:16 am

  #1085528

  http://msdn.microsoft.com/en-us/library/ms188004%28SQL.90%29.aspx

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  December 1, 2009 at 7:36 am

  #1085798

  msdb.dbo.restorehistory table as well.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• ALI SQLDBA

  SSCrazy

  Points: 2121

  More actions

  December 1, 2009 at 7:45 am

  #1085812

  From the object explorer if you navigate to system tables in MSDB database you will find a table "dbo.log_shipping_secondary_databases". query against this table. It will give you the last restored file and last restoration time.

  Hope this Helps 🙂