January 18, 2012 at 3:28 am
I have setup a log shipping and I can see my log files are going toup my DR server,so I used the below script ( stored proc) to setup my DR sql server. but when I like to test database I run the below script to bring them out of restoing mode and I see they didn't load any log file at all, so it doesn't work.
any help?
( may database is auditDB )
script for ending restoring mode:
script for setup logshipping in DR:
ALTER proc [dbo].[sp_SetLogShippingRestore]
@dbName as varchar(100)
,@myPrimary_server as varchar(100) --= 'harphsql'
,@backup_source_directory as varchar(100) --= '\\10.62.6.55\SQLBackup\Hourly\'
,@backup_destination_directory as varchar(100) --='\\turvmsqlBU\e$\LogShipRestore\'
as
--
-- ******* parameter
--declare @backup_source_directory as varchar(100) = N'\\10.62.6.55\SQLBackup\Hourly\' + @dbName
set @backup_source_directory = @backup_source_directory + @dbName
--declare @backup_destination_directory as varchar(100) = N'\\turvmsqlBU\e$\LogShipRestore\'+ @dbName
set @backup_destination_directory = @backup_destination_directory + @dbName
declare @copy_job_name as varchar(100) = N'LSCopy_sql_' + @dbName
declare @restore_job_name as varchar(100) = N'LSRestore_sql_' + @dbName
-- ****** Begin: Script to be run at Secondary: [turphsql] ******
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 = @myPrimary_server
,@primary_database = @dbName --N'WebNF'
,@backup_source_directory =@backup_source_directory
,@backup_destination_directory = @backup_destination_directory
,@copy_job_name = @copy_job_name
,@restore_job_name =@restore_job_name
,@file_retention_period = 20160
,@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_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 = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20100628
,@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 = @dbName
,@primary_server = @myPrimary_server
,@primary_database = @dbName
,@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__RestoreJobId
,@enabled = 1
END
-- ****** End: Script to be run at Secondary: [turvmsqlBU] ******
January 18, 2012 at 7:23 am
any errors from the log restore job?
any evidence in the errorlog of logs being restored?
what does query
select * from msdb..log_shipping_monitor_secondary
say about last copied and restored files?
---------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply