Server Name Changed & Can't disable Jobs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Server Name Changed & Can’t disable Jobs

Production SQL server: SQL1 (running on windows server named WIN1)
Standby SQL server: SQL2 (running on windows server named WIN2)
SQL2 is designated as a linked server in EM on SQL1. I setup the backup and log shipping on SQL1 and now want to test the failover process to SQL2. I’m doing it via EM so I can get a feel for what’s going on before I automate the process. Since SQL2, is only used for DR, I restored the master, msdb and model DB’s from the SQL1. Then I restored the application DB’s and and transaction files from SQL1. All went fine. The application DB’s will continue to be updated by via log shipping are designated as "Loading" in EM. *** MY QUESTIONS: When I attempted to disable a SQL Server Agent job on SQL2 it displays: error "14274: Can not edit, delete any jobs or schedules that originated from a MSX server. Any Ideas Why? and how to correct? Also, the standby SQL Server is now named SQL1.
Will this be a problem when production server SQL1 issues the restore command to be run on Standby server SQL2 (which is now SQL1 since the master DB from Production was restored to Standby)?
The MSX server error happens when you try to modify jobs and the originating_server column in sysjobs is different than the actual name of the SQL Server instance. You have to update the column in that table for the issue to go away. You shouldn’t have restored the system database to the standby server. You are going to have all kinds of issues if you are using SQL Server users because they are now all going to be out of synch and need to be fixed. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
The reason I restored the Master DB was in this document: It says:
Synchronize SQL Server Login IDs
Backup the Master database on the production server and restore it onto the standby server just before you begin the log shipping process. And should you ever have to failover your server, you will then have to restore the Master database again in order to keep your user accounts in synch. I guess I went to far when I restored the msdb and model DB’s from the production to standby server. I will retry by only restoring the master DB from the production server to the standby server. Would this be your recommendation? Does restoring the Master DB rename the SQL Server?

No restoring master database is nothing but updating values to the last good known data. Refer to the books online about rebuilding master database or restoring master database, and link fyi. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks for the responses, and sorry for the newbie questions. It appears the sql server name changes when I restore the master db from production to standby. Here is what I’ve done: All these steps are executed on the Standby server
net stop mssqlserver
Replace \standbymssql with a backup copy to what it was right after installing SQL Server
start sql server
osql -Usa -Pxxx
select @@servername ==> Standby
exit net stop mssqlserver
net start mssqlserver -c -m from EM restore master using master.bak created on Production SQL server
start sql server
osql -Usa -Pxxx
select @@servername ==> Production
exit It appears the SQL Server name has changed. Has it?
Will this cause problems/issues:
Restoring DB’s or Logs?
Executing procedures on the Standby linked server when I automate log shipping?
Failing Over to the Standby server? Can you give me some recommendations?
Use SP_DROPSERVER and use SP_ADDSERVER to take new servername, if the servername doesn’t matches then the jobs will fail. Refer to books online for more information on specified SPs. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.