We're in the process of replacing our SQL Server and have decided that renaming the server itself is going to be much easier than changing everything else to point to a new name. We found instructions on changing the SQL Server instance name to match the computer name which look like this:
sp_dropserver 'OLDSERVER' sp_addserver 'NEWSERVER', local
Though SQL Enterprise Manager doesn't seem to like those together. I had to change it to the following to make it work together:
sp_dropserver 'OLDSERVER'; GO sp_addserver 'NEWSERVER', 'local'; GO
Which is not bad, but I would prefer things to be more automated. Since @@ServerName returns the name of the instance, I figured out how to automate the first line:
sp_dropserver @@ServerName; GO
I also learned that SERVERPROPERTY('ServerName') is supposed to return the computer name, so I thought I could maybe use that to automate the second part, but this didn't work:
sp_addserver SERVERPROPERTY('ServerName'), 'local'; GO
I tried setting a variable, which I need to do anyway to update the SQL Agent jobs, but that didn't work either:
DECLARE @srv sysname; SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname); sp_addserver @srv, 'local'; GO
Incorrect syntax near 'sp_addserver'.
I would very much like to not have to hardcode the new server name into the script, to make this more easily reusable. Anybody got any ideas?