Invalid object name ‘msdb.dbo.cdc_jobs’

This past weekend, I had to migrate a sql instance from an old UCS blade to a new one.  The new blade would boot from the ZFS san instead of the retarded netapp san.  It might be retarded because of all the hands that has been in it but suffice to say, it suck.

I ran into two unknowns during this migration

This SQL instance is using CDC but not the built in CDC but the Attunity CDC found in the feature pack.  The CDC pulls data from oracle and dump it into 4 different SQL databases.  I would backup and restore them using SQL Litespeed v8.2, only later to find out that Litespeed doesn’t have the option: KEEP_CDC in its restore command!

This meant that I got an error stating the system CDC tables were not present and the Attunity CDC service took a crap.  I ended up using SQL native backup and restoring the databases with the hint: WITH KEEP_CDC.  this gave me back up my CDC tables.

But one more thing I didn’t know about!!!  The Attunity did some stuff to the MSDB database or maybe it is just how normal CDC work.  I didn’t really pay attention when I set it up a long time ago.  With CDC, there is normally a clean up job per CDC stream.  This job would look at MSDB.dbo.cdc_jobs.

Normally, I would simply restore the msdb database from the old blade to the new one, except, I have already moved them with idera’s admin toolset and I have added some additional one.  I didn’t want to redo all that work again.  The result was the following:

Script out the msdb.dbo.cdc_jobs table from the old blade. export the data out into a csv file.  change the database id and job_id in the csv file.  Create the table on the new blade, import the data into a staging table, insert the data from the staging table into the cdc_jobs table.  run: EXECsys.sp_MS_marksystemobject ‘dbo.cdc_jobs’ and that changed the table from a user to system.

I had to use a staging table because SSIS was giving me some trouble with the data type.