Log Shipping Restore Log on Secondary DB not restoring

  • Jeff Langdon

    SSC-Addicted

    Points: 410

    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: 109

    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

    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: 23197

    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.

  • Adam Haines

    SSC-Insane

    Points: 23197

    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.

  • DBA328

    Hall of Fame

    Points: 3952

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

  • Jeff Langdon

    SSC-Addicted

    Points: 410

    Adam Haines (9/13/2012)


    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.

    Looks like you are right about not finding the correct trn file. The final message in the Restore Agent History is "Skipped log backup file. Could not find a log backup file that could be applied to secondary database". It states that it restored 0 and that it ran successfully.

    What are the reasons the Agent Job could find the correct trn? The first job, back up log, runs every hour at the top of the hour. The second job, copying the trn file to local folder, runs at 15 minutes past, and the final job, restoring the log backup runs at 30 past the hour.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Jeff Langdon (9/13/2012)


    Adam Haines (9/13/2012)


    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.

    Looks like you are right about not finding the correct trn file. The final message in the Restore Agent History is "Skipped log backup file. Could not find a log backup file that could be applied to secondary database". It states that it restored 0 and that it ran successfully.

    What are the reasons the Agent Job could find the correct trn? The first job, back up log, runs every hour at the top of the hour. The second job, copying the trn file to local folder, runs at 15 minutes past, and the final job, restoring the log backup runs at 30 past the hour.

    Path is incorrect is most common cause of that. Other is that permissions on directory are insufficient, although I think you might get a different error in that case.

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

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Also, can you expand out the restore agent history and get the detailed error message?

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

  • Jeff Langdon

    SSC-Addicted

    Points: 410

    TheSQLGuru (9/13/2012)


    Also, can you expand out the restore agent history and get the detailed error message?

    Here is the history, finally message trunacted, but you get the idea.

  • Adam Haines

    SSC-Insane

    Points: 23197

    Make sure the SQL Server accounts (DB Engine & Agent) running the secondary instance have permission to the directory where the backups are stored and permissions to the share itself. You can even try do a test restore manually with a backup in that directory, to see if the DB Engine has permission to that share/directory.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Hmm - " ... that could be applied to secondary".

    I think you are missing one or more log files BEFORE the ones that are in the restore directory maybe? They are there, and you can see them (has actual file/path name in error message). Check to see if you have any gaps in the log file series, or if something was done on primary to interupt the LSN chain.

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

  • Adam Haines

    SSC-Insane

    Points: 23197

    After seeing the screenshot, I tend to agree with Kevin.. It is likely a LSN problem here.

  • Perry Whittle

    SSC Guru

    Points: 233779

    take a differential backup on the primary and restore it to the secondary to restart LS

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Adam Haines

    SSC-Insane

    Points: 23197

    You can run

    restore headeronly against your backup files and look at the databasebackuplsn and then you can walkthrough the log backup lsn chain from here and make sure they match.

    ex.

    restore headeronly from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test.bak'

    restore headeronly from disk =N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test_Log2.trn'

    restore headeronly from disk =N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test_Log3.trn'

  • Viewing 15 posts - 1 through 15 (of 17 total)

    You must be logged in to reply to this topic. Login to reply