Using a batch file to change the hostname in a stand-alone instance of SQL Server

Recently I was asked for a DOS batch script, that a tech person could run, to give a stand-alone instance of SQL Server the new hostname of its server.  This batch file would be run once the server name was changed.

This batch file removed the old hostname from SQL Server, and pulled the new computer name from the operating system’s environmental variables.  Then it stops & restarts SQL Server service.  The net start & net stop should reflect the current name of the SQL Server service, and may need to also restart the SQL Agent service.

Note: Normally I am asked to do this in PowerShell, but these are older servers that do not have PoweShell installed.  Therefore the script had to be done as a DOS batch script.  The script assumes local administrator privileges & that sqlcmd is in the executable search path.

DOS Batch Script:

:what SQL thinks the old server hostname is
sqlcmd -E -Q "select @@servername as 'Old Hostname';"

:SQL drops the old server name
sqlcmd -E -Q "exec sp_dropserver @@servername"

:Get the New Computer Name
SET newcomputername = %COMPUTERNAME%

ECHO **** The New Computer Name is : %newcomputername %

:SQL adds the new servername
sqlcmd -E -v new=%newcomputername %  -Q "exec sp_addserver  '$(new)','local'"

:Stop & Restart SQL Server
net stop mssqlserver

net start mssqlserver

:SQL Server displays new servername
sqlcmd -E -Q "select @@servername;"

:pause to check for errors before exiting
pause