locked
Log shipping fails RRS feed

  • Question

  • Hi,

    This is my scenario, both are SQL server 2017 Std:

    Srv1        ->            Srv2
    ---------------------------------

    LS_Backup              LS_Copy(Error)                  
    (No problems)         LS_Restore(Error)

    LS_Copy:

    *** Error: Could not retrieve copy settings for secondary ID 'b8a7b27b-fb81-4c33-b859-1e84a4b37145'.(Microsoft.SqlServer.Management.LogShipping) ***
    2018-08-13 22:45:12.76 *** Error: The specified agent_id B8A7B27B-FB81-4C33-B859-1E84A4B37145 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

    LS_Restore:

    *** Error: Could not retrieve restore settings.(Microsoft.SqlServer.Management.LogShipping) 
    2018-08-13 23:15:00.94 *** Error: The specified agent_id B8A7B27B-FB81-4C33-B859-1E84A4B37145 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

    A lot of people has solved this problem with this LINK but it did not worked for me because my jobs are correctly configured:

    On Srv2 LS_Copy:
    C:\Program Files\Microsoft SQL Server\140\Tools\Binn\sqllogship.exe" -Copy B8A7B27B-FB81-4C33-B859-1E84A4B37145 -server Srv2

    On Srv2 LS_Restore:
    C:\Program Files\Microsoft SQL Server\140\Tools\Binn\sqllogship.exe" -Restore B8A7B27B-FB81-4C33-B859-1E84A4B37145 -server Srv2

    • Srv2 was upgraded from 2008 to 2017
    • Both servers are now 2017
    • Just one instance on each server

    Any ideas?


    G.Waters



    Tuesday, August 14, 2018 4:03 AM

Answers

  • This is weird.

    The problem was solved by not using the secondary server as monitor (nor the primary)

    I used to have the secondary as monitor when using SQL 2008 R2.

    I think I'll never know if SQL 2017 does not allow this scenario intentionally.

    Thanks for your time folks !!!


    G.Waters

    • Marked as answer by George Waters Wednesday, August 15, 2018 8:24 PM
    Wednesday, August 15, 2018 8:24 PM

All replies

  • George so this failure started after you upgraded to SQL Server 2017. Was LS configured when server was running SQL Server 2008 ? As I see both copy and restore jobs are failing are they failing with same message. If nothing helps I would simply recreate the logshipping.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, August 14, 2018 4:12 AM
  • Hi Shashank,

    Sorry, I updated the thread so I show the error on each job.

    No, primary server was not configured before.

    Primary server has no problems, backup process is running just fine.


    G.Waters

    Tuesday, August 14, 2018 4:31 AM
  • Hi George Waters,

    Do you mean that the Primary is configured on SQL Server 2017 and Secondary is configured on SQL Server 2008 and upgraded to 2017?

    The error message indicates that "Could not retrieve restore settings", besides the wrong configuration, this problem can also be related to permission of SQL server agent.

    What's the result after running "C:\Program Files\Microsoft SQL Server\140\Tools\Binn\sqllogship.exe" -Copy B8A7B27B-FB81-4C33-B859-1E84A4B37145 -server Srv2"? 

    Besides, I would suggest you reconfigure the log shipping.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 14, 2018 7:09 AM
  • Hi Shashank,

    Sorry, I updated the thread so I show the error on each job.

    No, primary server was not configured before.

    Primary server has no problems, backup process is running just fine.


    G.Waters

    The problem mostly happens because of incorrect matching between secondary server ID.

    Please post output of below

    select secondary_server, secondary_id from msdb.dbo.log_shipping_monitor_secondary

    Does it matches with B8A7B27B-FB81-4C33-B859-1E84A4B37145, this is what seondary_id which LS copy and restore job is seeing.

    Does below gives any result ?

    select primary_server, primary_id from msdb.dbo.log_shipping_monitor_primary

    For the 2 queries you were running, run it by taking ID from first query i posted in this answer. msdb.dbo.log_shipping_monitor_secondary.

    Also let me know what u get as output from below after replacing ID from first query. Also make sure SRV2 should be name you get of secondary server from 1st query of log_shipping_monitor_secondary

    C:\Program Files\Microsoft SQL Server\140\Tools\Binn\sqllogship.exe" -Copy B8A7B27B-FB81-4C33-B859-1E84A4B37145 -server Srv2--replace ID from first query
    
    
    C:\Program Files\Microsoft SQL Server\140\Tools\Binn\sqllogship.exe" -Restore B8A7B27B-FB81-4C33-B859-1E84A4B37145 -server Srv2--replace ID from first query


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Tuesday, August 14, 2018 7:30 AM
  • Thanks Shashank,

    Well, I uninstalled the upgrade, so now both servers have a clean and full SQL 2017 std installation, but the problem remains.
    ------------------------------------------------------------------------------------------------------

    If I run your 2 queries on the secondary server, the result is: No rows returned

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

    If I run your 2 queries on the primary server, the result is: 

    select secondary_server, secondary_id from msdb.dbo.log_shipping_monitor_secondary
    No rows

    select primary_server, primary_id from msdb.dbo.log_shipping_monitor_primary

    primary_server         primary_id
    WIN-F0R5JPP4LP5 82F14C60-7DEB-4AED-9C4A-53D93C6303F4

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

    So I just don't understand what is wrong, I configure log shipping from primary:

    - Primary to copy backups to a shared folder in secondary, which is doing ok

    - Setting secondary server to copy database and initialize database from scratch

    - Setting secondary server as the monitor

    at the end, SQL server says the log shipping was correctly configured

    any ideas?


    G.Waters


    Wednesday, August 15, 2018 1:41 PM
  • This is weird.

    The problem was solved by not using the secondary server as monitor (nor the primary)

    I used to have the secondary as monitor when using SQL 2008 R2.

    I think I'll never know if SQL 2017 does not allow this scenario intentionally.

    Thanks for your time folks !!!


    G.Waters

    • Marked as answer by George Waters Wednesday, August 15, 2018 8:24 PM
    Wednesday, August 15, 2018 8:24 PM