Error and handling of SQL Server hostname change

After SQL Server changes the hostname, you need to do something, otherwise the maintenance plan and subscription publishing will have problems, the following steps:

Sometimes after the computer name is modified, the execution of the SELECT @ @servername still returns the original computer name, indicating that the "instance" has not been modified and needs to be performed manually:

Sp_dropserver ' original host name '

And then execute
sp_addserver ' new host name ', ' local '
Then, restart the database service, then execute SELECT @ @servername will return the modified computer name, indicating that "instance" has been modified.



Verified:
Select CONVERT (varchar (+), serverproperty (' ServerName '))
Select CONVERT (varchar (+), @ @servername)

Specific as follows:

1. Disable Publishing
2, exec sp_dropserver ' original host name ', ' Droplogins '
3, sp_addserver ' new host name ', ' local '

Note the unity of case and select CONVERT (varchar (+), serverproperty (' ServerName '))

You can also execute the following script:

--Execute the following statement, and then restart the SQL service after completion
If SERVERPROPERTY (' servername ') <> @ @servername
Begin
declare @server sysname
Set @server = @ @servername
exec sp_dropserver @server = @server
Set @server = Cast (serverproperty (' servername ') as sysname)
exec sp_addserver @server = @server, @local = ' local '
End

Do the above to avoid the following errors

Following are changes to the computer name after the maintenance plan has changed how to proceed

[298] SQL Server error: 15404, unable to get information about Windows NT Group/user ' Hrb-lg\administrator ', error code 0x534. [SQLSTATE 42000] (connisloginsysadmin)

I have not encountered this problem before, but look at this error message: Unable to get information about Windows NT Group/user ' Win-i556ub3odg2\administrator '. This is mostly because of the owner relationship, the default owner is the current user when the job is established, but the user of the computer does not necessarily match the owner of the SQL.

Open the general interface of the job and change the "Win-i556ub3odg2\administrator" to: "sa".

SQL Server error: 15404, unable to get information about Windows NT Group/user Mypc\administrator ', error code 0x534. [SQLSTATE 42000] (connisloginsysadmin)

When our system user name changes, we will find that the previous well-run maintenance plan in SQL has suddenly failed all of a sudden and the above error has occurred. The workaround is to change the original system user name in SQL security >> logins to the user name now used, and then delete the existing maintenance plan to re-establish the maintenance plan.

Error and handling of SQL Server hostname change