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.

Comments

comments

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. Required fields are marked *