Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Log Shipping Jobs on Secondary not getting created Expand / Collapse
Author
Message
Posted Wednesday, March 21, 2012 2:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:35 AM
Points: 239, Visits: 369
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
Post #1270489
Posted Wednesday, March 21, 2012 2:49 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 6:49 PM
Points: 681, Visits: 428
what is the error message?
Post #1270506
Posted Wednesday, March 21, 2012 2:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:35 AM
Points: 239, Visits: 369
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
Post #1270509
Posted Wednesday, March 21, 2012 2:56 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 6:49 PM
Points: 681, Visits: 428
Can u tell what does the copy job history say?
Post #1270517
Posted Wednesday, March 21, 2012 3:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:35 AM
Points: 239, Visits: 369
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
Post #1270521
Posted Thursday, March 22, 2012 11:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:35 AM
Points: 239, Visits: 369
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
Post #1271089
Posted Friday, March 23, 2012 7:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:23 AM
Points: 23, Visits: 153
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
Post #1271623
Posted Friday, March 23, 2012 7:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:35 AM
Points: 239, Visits: 369
Thanks. I have attempted this multiple times. The jobs never get created.



Del Lee
Post #1271630
Posted Friday, March 23, 2012 7:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:23 AM
Points: 23, Visits: 153
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.
Post #1271638
Posted Friday, March 23, 2012 7:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:35 AM
Points: 239, Visits: 369
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
Post #1271648
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse