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] ******