Making a fool out of MSDB

I was restoring a SQL Server instance on a different server for DR purposes – including system databases. What I have overlooked was the fact that restoring the msdb database would mean keeping the existing settings of the old instance into the new one. While I was trying to delete the database maintenance plans and the jobs, I kept getting an MSX-related error which prevented me from deleting the jobs. I looked at the jobs by running the sp_help_job system stored procedure and found out that the originating_server column happens to be the name of my old SQL Server instance. This was the primary reason why I could not delete the jobs either from Enterprise Manager or running the sp_delete_job system stored procedure.

To workaround that issue, I simply modified the originating_serverĀ column of the sysjobs table to the name of the current instance. After that, I was able to delete the database maintenance plans and the jobs. Now, my server is ready for DR. Log shipping configuration is the next thing to do.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.