In this post, I will demonstrate how to rename the server / instance in the database when the hostname changes in the operating system, which is relatively common in everyday life.
When this happens, the hostname in the operating system is different from the hostname in the database, and you may notice this when using functions such as @@ SERVERNAME, for example.
One way to always retrieve the updated information is to use the SERVERPROPERTY ('Servername') function, which retrieves the information directly from the operating system. When it brings a result other than @@ SERVERNAME, it means that the name registered in SQL Server is different from the real name of the machine.
To fix this kind of problem, simply run the following commands:
-- Para a instância padrão (MSSQLServer)
EXEC sp_dropserver 'nome_antigo'
EXEC sp_addserver 'nome_atual', local
-- Para instâncias nomeadas (ex: servidor\sql2016)
EXEC sp_dropserver 'servidor_antigo\instancia'
EXEC sp_addserver 'servidor_novo\instancia', local
After making these changes, restart the SQL Server service and the @@ SERVERNAME function will return the correct server name.
For more information, see the official Microsoft documentation. through this link.
Regards and see you next post.