Log Shipping Jobs on Secondary not getting created

  • Hi all:

    I'm trying to test my understanding of log shipping here. I'm currently trying to get it working on 2 servers for now. After going through the setup, all steps indicate a success. I can see the backup and alerts jobs successfully created on the primary server, but I do not see the copy and restore jobs on the secondary server. Oddly enough, I do see the Adventureworks data files on the secondary server and in SSMS I see it in "(restoring...)" mode on the secondary.

    I have both instances' sql server service & agent service running under the domain account we have set up for this. I have this domain account set up in the sql user and sql agent groups on both servers. The domain account has sysadmin rights on both servers. The domain account has modify permissions in the copy folder that I've set up on the secondary.

    What have I overlooked? Again, the problem seems to be with the secondary and specifically the jobs not getting created there.

    Thanks,



    Del Lee

  • what is the error message?

  • SS999 (3/21/2012)


    what is the error message?

    There is no error message. When Log Shipping is set up it reports all steps are a success.



    Del Lee

  • Can u tell what does the copy job history say?

  • SS999 (3/21/2012)


    Can u tell what does the copy job history say?

    There is no job history. Part of the problem is that the copy job is not getting created. Thus, there can be no history.



    Del Lee

  • Additional info:

    select @@servername returns NULL

    select ServerProperty('servername') returns 'SQLDR'

    The server is virtualized - I don't know the version of hypervisor. Previously, ServerProperty was returning 'TBS-01' until I did a sp_dropserver and sp_addserver to correct the issue.



    Del Lee

  • Hi!, well, if you do not have job, TLS will not works. You should try to script TLS configuration, pressing bottom 'Script Configuration', and create the job re-executing this backuped configuration. In deed, this is a good practice, so if you need to reconfiguring TLS, just uncheck 'Enable this a primary database in a log shipping configuration' option to delete previously created jobs, and by execution of this scripts, will be on road again.

    I hope this help you.

    Best regards

    eng. Fernando Paez Becker

  • Thanks. I have attempted this multiple times. The jobs never get created.



    Del Lee

  • Ok, please could you tell us what version of Ms Sql Server do you have? Can you copy created script configurations TLS, so we can find an workaround.

  • Using SQL 2005, SP3

    The source instance is named 2-BQZ5DP1\DELS2005. I have tried substituting the IP address as well. Thanks for giving this a look. 🙂

    -- ****** Begin: Script to be run at Secondary: [sqldr] ******

    DECLARE @LS_Secondary__CopyJobIdAS uniqueidentifier

    DECLARE @LS_Secondary__RestoreJobIdAS uniqueidentifier

    DECLARE @LS_Secondary__SecondaryIdAS uniqueidentifier

    DECLARE @LS_Add_RetCodeAs int

    EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary

    @primary_server = N'10.0.0.165\DELS2005'

    ,@primary_database = N'AdventureWorks'

    ,@backup_source_directory = N'\\SQL01\Databases\LSBackups'

    ,@backup_destination_directory = N'F:\LSCopies '

    ,@copy_job_name = N'LSCopy_2-BQZ5DP1\DELS2005_AdventureWorks'

    ,@restore_job_name = N'LSRestore_2-BQZ5DP1\DELS2005_AdventureWorks'

    ,@file_retention_period = 4320

    ,@monitor_server = N'10.0.0.165\DELS2005'

    ,@monitor_server_security_mode = 1

    ,@overwrite = 1

    ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT

    ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT

    ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

    BEGIN

    DECLARE @LS_SecondaryCopyJobScheduleUIDAs uniqueidentifier

    DECLARE @LS_SecondaryCopyJobScheduleIDAS int

    EXEC msdb.dbo.sp_add_schedule

    @schedule_name =N'DefaultCopyJobSchedule'

    ,@enabled = 1

    ,@freq_type = 4

    ,@freq_interval = 1

    ,@freq_subday_type = 4

    ,@freq_subday_interval = 15

    ,@freq_recurrence_factor = 0

    ,@active_start_date = 20120322

    ,@active_end_date = 99991231

    ,@active_start_time = 0

    ,@active_end_time = 235900

    ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT

    ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

    EXEC msdb.dbo.sp_attach_schedule

    @job_id = @LS_Secondary__CopyJobId

    ,@schedule_id = @LS_SecondaryCopyJobScheduleID

    DECLARE @LS_SecondaryRestoreJobScheduleUIDAs uniqueidentifier

    DECLARE @LS_SecondaryRestoreJobScheduleIDAS int

    EXEC msdb.dbo.sp_add_schedule

    @schedule_name =N'DefaultRestoreJobSchedule'

    ,@enabled = 1

    ,@freq_type = 4

    ,@freq_interval = 1

    ,@freq_subday_type = 4

    ,@freq_subday_interval = 5

    ,@freq_recurrence_factor = 0

    ,@active_start_date = 20120322

    ,@active_end_date = 99991231

    ,@active_start_time = 0

    ,@active_end_time = 235900

    ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT

    ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

    EXEC msdb.dbo.sp_attach_schedule

    @job_id = @LS_Secondary__RestoreJobId

    ,@schedule_id = @LS_SecondaryRestoreJobScheduleID

    END

    DECLARE @LS_Add_RetCode2As int

    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

    BEGIN

    EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database

    @secondary_database = N'AdventureWorks'

    ,@primary_server = N'10.0.0.165\DELS2005'

    ,@primary_database = N'AdventureWorks'

    ,@restore_delay = 0

    ,@restore_mode = 0

    ,@disconnect_users= 0

    ,@restore_threshold = 45

    ,@threshold_alert_enabled = 1

    ,@history_retention_period= 5760

    ,@overwrite = 1

    END

    IF (@@error = 0 AND @LS_Add_RetCode = 0)

    BEGIN

    EXEC msdb.dbo.sp_update_job

    @job_id = @LS_Secondary__CopyJobId

    ,@enabled = 1

    EXEC msdb.dbo.sp_update_job

    @job_id = @LS_Secondary__RestoreJobId

    ,@enabled = 1

    END

    -- ****** End: Script to be run at Secondary: [sqldr] ******



    Del Lee

  • This script is good enough and it must create those JOBS...

    Did you try to execute this script at destination server without get any has an output? sems quit perplexing

    Well, then.... You should create manually each job, (copy & restore).

    Copy

    Naming: LSCopy_sourcesrvname,pornumber_DBSource

    , step 1, type Operating system (cmdexec)

    "C:\apps_srv\mssql\100\Tools\Binn\sqllogship.exe" -Copy "NRO_GUID_1" -server "destination_server_instance"

    Restore

    Naming: LSRestore_sourcesrvname,pornumber_DBDestination

    , step 1, type Operating system (cmdexec)

    "C:\apps_srv\mssql\100\Tools\Binn\sqllogship.exe" -Restore "NRO_GUID_1" -server "destination_server_instance"

    NRO_GUID_1-> id in msdb.dbo.log_shipping_secondary create by some like

    insert into msdb.dbo.log_shipping_secondary(NEWID(),'sourceserver,portnumber',

    primary_database,

    backup_source_directory,

    backup_destination_directory,

    file_retention_period,

    copy_job_id ,--> id from select * from sysjobs create early

    restore_job_id ,--> id from select * from sysjobs create early

    monitor_server,

    monitor_server_security_mode,

    user_specified_monitor,

    last_copied_file - null,

    last_copied_date -null)

    So, first create both jobs with "id_guid" as a text, then insert into table take guid number and edit job command as needeed... and tell us how it work.

    Regards! FPB

  • Thanks, Al-Rahim, I may try that later. Since this is a test, I have also tried log shipping to another secondary. For those trials, the jobs get created successfully, but the copy job fails with an "Could not retrieve copy settings for secondary id '*****'. Then, I tried to log ship to this same secondary without providing a monitor. In this case, all is working.

    I am continuing to research this, as I need to logship to the original secondary and I must have a monitor.



    Del Lee

  • Good Morning Del Lee,

    I too facing the same issue.Did you get the solution for this issue?.

    It will be great if you share it.

    Regards

  • Did you get the solution for this issue?

    No, I was never able to get this resolved. In the end, I believe it was a network issue because the secondary was on a different network. I'm sure there is a solution for this scenario, but it was not critical in our case to pursue it beyond what I had already done. Good luck with your situation.



    Del Lee

  • The error refers to an account entitlement error... so give properly permissions to secondary agent server account. You should have same account in both primary and secondary servers. Also see permissions at windows level (the secondary agent server account should have read permission for destination backup folder)

    That will solve your issue.. Please, let us know.

    Regards.

Viewing 15 posts - 1 through 15 (of 19 total)

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