Log Shipping Restore Log on Secondary DB not restoring

• Jeff Langdon

  SSC-Addicted

  Points: 410

  More actions

  September 13, 2012 at 7:32 am

  #261796

  I have a SQL 2000 database that was migrated to a SQL 2008 R2 instance but the compatibility mode still remains at 80. I am trying to set up log shipping to another instance in a cluster also a SQL 2008 R2 instance. All 3 agent jobs run successfully but the secondary database is never updated.

  The first job backs up the primary, I see the trn files created. The second job copies the trn file to the local folder, I can see them too. The final job that restores the log file on the secondary database runs successfully, but doesn't update the database so data/users on the primary are not getting moved over to the secondary.

  The SQL error logs show no errors. I looked into running a trace, but I don't see events specific to log shipping. At this point, I don't know how to troubleshoot this problem.

  Thanks for any help,

  Jeff Langdon

  Update:

  I have run the following queries but nothing stands out, except for when I query the restorehistory table I get nothing which is expected, I guess. 🙂

  USE msdb;

  GO

  select * from log_shipping_secondary

  select * from log_shipping_monitor_secondary

  SELECT * from dbo.sysjobs WHERE category_id = 6

  SELECT * FROM [msdb].[dbo].[sysjobhistory]

  where [message] like '%Operating system error%'

  order by [run_date] desc , [run_time]

  SELECT * FROM [msdb].[dbo].[log_shipping_monitor_error_detail]

  where [message] like '%Operating system error%'

  SELECT * FROM [msdb].[dbo].[restorehistory]

  Order by restore_date desc

• muaddba

  SSC Enthusiast

  Points: 140

  More actions

  September 13, 2012 at 7:42 am

  #1537198

  Jeff, what happens if you run the command in the job manually? Have you traced through that command to see what it does?

• Jeff Langdon

  SSC-Addicted

  Points: 410

  More actions

  September 13, 2012 at 7:45 am

  #1537200

  crazyjoe (9/13/2012)

  ---

  Jeff, what happens if you run the command in the job manually? Have you traced through that command to see what it does?

  Basically the same, it runs succesfully, but nothing gets updated. Not sure what you mean by "traced through that command". Could you explain?

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  September 13, 2012 at 7:49 am

  #1537202

  Even though the job reports success, I would still look at the agent history and in the second step from the top. There should be some detail about why it wasnt able to perform any work. My guess off the top of my head is a permissions issue.

  ---

  
  My blog: http://jahaines.blogspot.com

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  September 13, 2012 at 7:54 am

  #1537212

  Another thought is maybe the restore job is looking in the wrong directory, and the job doesn't find any backups that need restoring, so it reports success.

  ---

  
  My blog: http://jahaines.blogspot.com

• DBA328

  Hall of Fame

  Points: 3952

  More actions

  September 13, 2012 at 7:57 am

  #1537216

  check the restore job history and there should be some useful information.