March 22, 2018 at 10:26 am
I have created SQL Server 2008 R2 Server from existing SQL Server. It has different name, so I have renamed the server. However, all the Jobs on the new server are still pointing to old server(because it's copy of old server) which has approx. 150 Jobs. I want to ensure all the Jobs run successfully. Checking to see if there is a script which can change all the Jobs with new server name rather than doing manually from Management studio?
Thanks
March 22, 2018 at 12:07 pm
Admingod - Thursday, March 22, 2018 10:26 AMI have created SQL Server 2008 R2 Server from existing SQL Server. It has different name, so I have renamed the server. However, all the Jobs on the new server are still pointing to old server(because it's copy of old server) which has approx. 150 Jobs. I want to ensure all the Jobs run successfully. Checking to see if there is a script which can change all the Jobs with new server name rather than doing manually from Management studio?Thanks
There was a change many versions ago so that the jobs would point to the local server - originating server id instead of the server name.
So in sysjobs, are the jobs pointing to a server other than 0? Or is the issue that the server name for SQL Server is incorrect - did you rename the server after SQL Server was installed?
Check the server id in sysjobs and also see if you get the correct server name with select @@SERVERNAME
If you rename a server where SQL Server is installed, you usually have to rename the server in SQL Server as well. sp_dropserver 'old_name'
go
sp_addserver 'new_name', 'local'
go
This article explains it further:
Rename a Computer that Hosts a Stand-Alone Instance of SQL Server
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply