SQL Server – Log Shipping – Error – “Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41”

Background

Applying Log Shipping script, but getting the error pasted below.

 

Error

Textual


Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41
The specified @job_id ('46B4CCA0-8420-4FBF-AE1E-087F4A7F712E') does not exist.

Image
TheSpecifiedJobDoesNotExist

List MSDB Jobs


	-- exec msdb.dbo.sp_help_job
	declare @database sysname
	declare @databaseJob sysname

	set @database = 'NetTraffic'
	set @databaseJob = 'LogShipping_' + @database + '_db_log'

	select 
			  [database] = @database
			, job_id
			, [name]
			, [description]
			, date_created
	from msdb.dbo.sysjobs 
	where name = @databaseJob



ListDBJobs-LogShipping-DBLog.20160201

Code

Original Code

Here is the original code.




-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [DBPRIMARY].[NetTraffic_db],
-- The script needs to be run at the Primary in the context of the [msdb] database.  
------------------------------------------------------------------------------------- 
-- Adding the Log Shipping configuration 

-- ****** Begin: Script to be run at Primary: [DBPRIMARY] ******


DECLARE @LS_BackupJobId	AS uniqueidentifier 
DECLARE @LS_PrimaryId	AS uniqueidentifier 
DECLARE @SP_Add_RetCode	As int 


EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
		@database = N'NetTraffic_db' 
		,@backup_directory = N'E:\SQLBackups\MSSQL\NetTraffic_db' 
		,@backup_share = N'\\DBPRIMARY\SQLBackups\MSSQL\NetTraffic_db' 
		,@backup_job_name = N'LogShipping_NetTraffic_db_log' 
		,@backup_retention_period = 2160
		,@backup_threshold = 60 
		,@threshold_alert_enabled = 1
		,@history_retention_period = 5760 
		,@backup_job_id = @LS_BackupJobId OUTPUT 
		,@primary_id = @LS_PrimaryId OUTPUT 
		,@overwrite = 1 


IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_BackUpScheduleUID	As uniqueidentifier 
DECLARE @LS_BackUpScheduleID	AS int 


EXEC msdb.dbo.sp_add_schedule 
		@schedule_name =N'LogShipping_NetTraffic_db_log' 
		,@enabled = 1 
		,@freq_type = 4 
		,@freq_interval = 1 
		,@freq_subday_type = 4 
		,@freq_subday_interval = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20100616 
		,@active_end_date = 99991231 
		,@active_start_time = 20000 
		,@active_end_time = 230000 
		,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 
		,@schedule_id = @LS_BackUpScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
		@job_id = @LS_BackupJobId 
		,@schedule_id = @LS_BackUpScheduleID  

EXEC msdb.dbo.sp_update_job 
		@job_id = @LS_BackupJobId 
		,@enabled = 1 


END 


EXEC msdb.dbo.sp_update_job 
		@job_id = N'46b4cca0-8420-4fbf-ae1e-087f4a7f712e' 
		,@enabled = 1 

EXEC master.dbo.sp_add_log_shipping_alert_job 

EXEC master.dbo.sp_add_log_shipping_primary_secondary 
		@primary_database = N'NetTraffic_db' 
		,@secondary_server = N'DBSECONDARY' 
		,@secondary_database = N'NetTraffic_db' 
		,@overwrite = 1 

-- ****** End: Script to be run at Primary: [DBPRIMARY]  ******


Revision

And, here is the revised code.



begin tran


	-- Execute the following statements at the Primary to configure Log Shipping 
	-- for the database [DBPRIMARY].[NetTraffic_db],
	-- The script needs to be run at the Primary in the context of the [msdb] database.  
	------------------------------------------------------------------------------------- 
	-- Adding the Log Shipping configuration 

	-- ****** Begin: Script to be run at Primary: [DBPRIMARY] ******


	DECLARE @LS_BackupJobId	AS uniqueidentifier 
	DECLARE @LS_PrimaryId	AS uniqueidentifier 
	DECLARE @SP_Add_RetCode	As int 


	EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
			@database = N'NetTraffic_db' 
			,@backup_directory = N'E:\SQLBackups\MSSQL\NetTraffic_db' 
			,@backup_share = N'\\DBPRIMARY\SQLBackups\MSSQL\NetTraffic_db' 
			,@backup_job_name = N'LogShipping_NetTraffic_db_log' 
			,@backup_retention_period = 2160
			,@backup_threshold = 60 
			,@threshold_alert_enabled = 1
			,@history_retention_period = 5760 
			,@backup_job_id = @LS_BackupJobId OUTPUT 
			,@primary_id = @LS_PrimaryId OUTPUT 
			,@overwrite = 1 


	IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 
	BEGIN 

	DECLARE @LS_BackUpScheduleUID	As uniqueidentifier 
	DECLARE @LS_BackUpScheduleID	AS int 


	EXEC msdb.dbo.sp_add_schedule 
			@schedule_name =N'LogShipping_NetTraffic_db_log' 
			,@enabled = 1 
			,@freq_type = 4 
			,@freq_interval = 1 
			,@freq_subday_type = 4 
			,@freq_subday_interval = 15 
			,@freq_recurrence_factor = 0 
			,@active_start_date = 20100616 
			,@active_end_date = 99991231 
			,@active_start_time = 20000 
			,@active_end_time = 230000 
			,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 
			,@schedule_id = @LS_BackUpScheduleID OUTPUT 

	EXEC msdb.dbo.sp_attach_schedule 
			@job_id = @LS_BackupJobId 
			,@schedule_id = @LS_BackUpScheduleID  

	EXEC msdb.dbo.sp_update_job 
			@job_id = @LS_BackupJobId 
			,@enabled = 1 


	END 


        /*
            Replace hard-coded Job ID with value returned from master.dbo.sp_add_log_shipping_primary_database 
        */
	EXEC msdb.dbo.sp_update_job 
			--@job_id = N'46b4cca0-8420-4fbf-ae1e-087f4a7f712e' 
			 @job_id = @LS_BackupJobId
			,@enabled = 1 

	EXEC master.dbo.sp_add_log_shipping_alert_job 

	EXEC master.dbo.sp_add_log_shipping_primary_secondary 
			@primary_database = N'NetTraffic_db' 
			,@secondary_server = N'DBSECONDARY' 
			,@secondary_database = N'NetTraffic_db' 
			,@overwrite = 1 

	-- ****** End: Script to be run at Primary: [DBPRIMARY]  ******


rollback tran


Quick Explanation

  1. A quick explanation is that somehow the hard-coded Job ID ( passed to msdb.dbo.sp_update_job) does not match the actual Job ID
  2. To correct we are using the Job ID returned from the master.dbo.sp_add_log_shipping_primary_database function
  3. As a safety guard, we enclosed everything in a transaction block
  4. Once confirmed that no errors are returned, please replace rollback tran with commit tran

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s