Duplicate key row in object 'dbo.bts_LogShippingJobs'

A customer was setting up Log Shipping disaster recovery for their BizTalk Server 2006 R2 environment. https://technet.microsoft.com/en-us/library/aa560961(v=bts.20).aspx  They had gotten to the step where they run the stored procedure bts_ConfigureBizTalkLogShipping. They received the error "Msg 2601, Level 14, State 1,  Procedure bts_ImportSQLAgentJobs, Line 56 Cannot insert duplicate key row in object 'dbo.bts_LogShippingJobs' with unique index 'CIX_LogShippingJobs'."

It turns out that this customer also had several of their own SQL Agent jobs running on the BizTalk server. As part of configuring the destination environment, we attempt to recover all of the jobs running on the BizTalk server with one exception: we don’t support importing of SQL Agent jobs where the steps use more than one database. The script's logic iterates over each database to be recovered and logs the jobs associated with that database in bts_LogShippingJobs for later recovery. If a job has more than one database association, the script attempts to log it twice. But we never want to recover the same job more than once, so bts_LogShippingJobs doesn't allow duplicate jobs. When the script attempts to log the job the second time, it fails.

In general, we discourage running any jobs on the SQL Server that supports BizTalk Server other than the jobs that ship with the product.

For those customers who choose to run their own jobs and encounter this issue, the solution is to temporarily remove any job(s) associated with multiple databases while setting up the destination recovery environment. You will also need to develop your own recovery plan for any such job(s). Once the destination environment is configured, you can restore the jobs. The following T-SQL will identify jobs associated with multiple databases:

SELECT j.name, COUNT(DISTINCT js.database_name) AS dbcount INTO #tmp FROM msdb.dbo.sysjobsteps js JOIN msdb.dbo.sysjobs j WITH (NOLOCK) ON j.job_id = js.job_id GROUP BY j.name SELECT * FROM #tmp WHERE dbcount > 1 DROP TABLE #tmp