Server Name Change - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Server Name Change

Author

Topic

MassiveOverkill
Starting Member

20 Posts

Posted - 2007-08-13 : 12:42:21

Forgive my newbness, but I need help with a couple of scripts. Computer name changes happen alot where I work and I'm trying to make a simple batch file that when run, will automatically update the SQL server name. I believe this will work, but I am having problems with the text formatting of the output file. Here is the 'code':

-------------start of batch file------------------

CLS

REM THIS BATCH FILE WILL UPDATE THE INSTANCE NAME ON A SERVER, WHERE THE PC NAME HAS BEEN CHANGED AFTER THE INSTALLATION OF SQL

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q "select @@servername" -o C:\OLDSERVERNAME.TXT"

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q EXEC sp_dropserver -i C:\OLDSERVERNAME.TXT"

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q EXEC sp_addserver '127.0.0.1', 'local'"

REM NET STOP MSSQLSERVER

REM NET START MSSQLSERVER

PAUSE

-------------end of batch file------------------

I guess I could also edit the loopback address to be the current PC Name, but this should work for my purposes I also know that I may have to change the authentication mode from SQL to Windows Authentication.

Also, is there any way to enable Named Pipes and TCP/IP connections via script, without having to use SQL Configuration Manager?

Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-13 : 15:36:38

You can't use loopback address for remote connection. And need modify registry to enable protocols if you don't want to do it in configuration manager.

MassiveOverkill
Starting Member

20 Posts

Posted - 2007-08-13 : 15:41:19

This batch is meant to be run on each machine, not as a remote connection. Thanks.

MassiveOverkill
Starting Member

20 Posts

Posted - 2007-08-14 : 12:10:50

Figured it out. As far as I know, no one else has come up with an automated process to do this.

-----------------------start of batch file----------------------
CLS

REM THIS BATCH FILE WILL UPDATE THE INSTANCE NAME ON A SERVER, WHERE THE PC NAME HAS BEEN CHANGED AFTER THE INSTALLATION OF SQL

%SYSTEMDRIVE% SET

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q "EXEC sp_dropserver @server = @@servername"

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q "EXEC sp_addserver %COMPUTERNAME%, 'local'"

NET STOP MSSQLSERVER

NET START MSSQLSERVER

PAUSE
-----------------------end of batch file----------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources