Changing server name - all procs, views, functions, etc

Question

Changing server name - all procs, views, functions, etc

krypto69

SSChampion

Points: 13609
More actions
July 8, 2014 at 1:53 pm

#288506

Hi,
I have to make a copy of our production DB and move it to a new server.
New server has different server name and different instance name and different DB name.
I used a utility about a year ago that I downloaded that would search thru everything and let me subsistute the old server info with the new server info. It would create the update scripts for me. For the life of me I can't find that utility.
Is there something I can use that will help me create an update script to rename all my objects with the new server info?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

How does the different name for Server, instance or database affect you? Do you make reference to them in your code/object definitions?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Why do you need to rename your objects? Do you have a naming standard that requires the server name to be part of table or procedure names?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13609 More actions · Answer 3

GilaMonster (7/8/2014)
Why do you need to rename your objects? Do you have a naming standard that requires the server name to be part of table or procedure names?

Our vendor is requiring us to go Saas. So we have to move all of our DB's into their environment. Their environment has different server name and DB name.

The DB's are schematically identical. Our procs typically have the 'USE [DATABASE NAME]. So they will no longer work.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

krypto69 (7/8/2014)
Our procs typically have the 'USE [DATABASE NAME]. So they will no longer work.

How does a procedure have a USE statement inside it?

CREATE PROCEDURE Test

AS

USE master

SELECT * FROM sys.objects AS o

go

Msg 154, Level 15, State 1, Procedure Test, Line 4

a USE database statement is not allowed in a procedure, function or trigger.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13609 More actions · Answer 5

okay scratch that..

some of the code within our procs/views/etc references the server name

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

They would reference a linked server name, which does not have to correspond with the actual server name. When you create the linked server on the new installation, keep the same name and change what server it points to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass