Wednesday, September 26, 2007
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 keep getting an MSX-related error which prevents 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.