SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Shipping Jobs on Secondary not getting created


Log Shipping Jobs on Secondary not getting created

Author
Message
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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
SS999
SS999
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 431
what is the error message?
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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
SS999
SS999
SSC Eights!
SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)SSC Eights! (853 reputation)

Group: General Forum Members
Points: 853 Visits: 431
Can u tell what does the copy job history say?
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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
Al-Rahim
Al-Rahim
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 159
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
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
Thanks. I have attempted this multiple times. The jobs never get created.



Del Lee
Al-Rahim
Al-Rahim
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 159
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.
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
DECLARE @LS_Add_RetCode As 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_SecondaryCopyJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleID AS 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_SecondaryRestoreJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID AS 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_RetCode2 As 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search