Rename Sql server Named Instance

Question

Hello,
I will be doing Migration from Sql server 2008R2 to Sql server 2014 but I need to keep the Source(Old) Sql Server instance name, Is it any issue?
I need to Rename the Named instance this time so and Renaming Instance name as Original Source Server name.

I will be taking following steps:

Source Server: Sql2008\MSS
Target Server: Sql2014\MSS

1) Backup All Sql2008\MSS instance databases and restore into Sql2014\MSS
2)Change DB compatibility to 2014 into Sql2014\MSS
3) Once Windows server getting renamed to Sql2008-Old (so we can ReUse this name for new server)
4) Go into Sql2014 server and run following T-Sql:

-- Run following into New Server Sql2014
EXEC sp_DROPSERVER 'Sql2008\MSS'

/* Execute below to add a new server name into New Server -Sql2014 */

EXEC sp_ADDSERVER 'Sql2014\MSS', 'local'

ReStart Sql server/Sql service

Anything I am missing?

Thank You for your Help!

Answer 1

I am not sure that I understand this.

If the server is called SQL2014 already, there is no need to do the dropserver/addserver dance.

Possibly the list above is missing a step were you rename the Windows server SQL2014 to SQL2008, but in that case you would do:

EXEC sp_dropserver 'SQL2014\MSS'
EXEC sp_addserver 'SQL2008\MSS', 'locall'

(May I guess that the actual server names are different and then you messed up the names when you composed your posting?)

Answer 2

Thanks Erland for quick Response!

You are right "We will be Renaming the Windows server SQL2014 to SQL2008" after Database Migrating
Sorry, I have messed up server name, you are right as I am putting back (Renaming back) old Sql Server Instance

It should be dropping New server name then adding old Server name back

EXEC sp_dropserver 'SQL2014\MSS' ==> After Migrating all the databases into this Sql2014\MSS then I am runing this command into SQL2014\MSS, right?

Then I will run this command also into SQL2014\MSS, Right?
EXEC sp_addserver 'SQL2008\MSS', 'local'

Answer 3

Thanks Erland.
I agreed to restart Sql Server.
We are using now a days Microsoft Virtual Account so no Local accounts pointing but good idea to check.
Can I know which column will indicate as it's showing lots of logins?