Edwin Daniel's profileMicrosoft technologies a...BlogLists Tools Help

Blog


    September 06

    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.