July 8, 2014 at 1:53 pm
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?
July 8, 2014 at 2:06 pm
How does the different name for Server, instance or database affect you? Do you make reference to them in your code/object definitions?
July 8, 2014 at 2:11 pm
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
July 8, 2014 at 2:54 pm
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.
July 8, 2014 at 2:59 pm
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
July 8, 2014 at 3:17 pm
okay scratch that..
some of the code within our procs/views/etc references the server name
July 8, 2014 at 3:35 pm
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply