When we restore on-premise SQL database from one environment to another environment, we may need to change linked server names which exist in the inside of source database’s stored procedures to new linked server name in the restored database. For example, if the source database name is “NetwovenDatabase” in on-premise SQL server and linked server name associated with “NetwovenDatabase” is “LinkedServer1” then after restoration of “NetwovenDatabase” to another on-premise SQL server, we need to change the associated linked server name from “LinkedServer1” to “LinkedServer2”.
We can change linked server name from “LinkedServer1” to “LinkedServer2” manually by opening procedures one by one but this is not an efficient way to do that. Hence, to do that in an efficient way we need to execute following scripts after restoring the database from one environment to another environment.
1. Connect restored database server using SSMS with administrative privilege.
2. Connect master database.
Use <restored database name> SET NOCOUNT ON DECLARE @searchFor VARCHAR(100), @replaceWith VARCHAR(100 DECLARE @count INT DECLARE @i INT =1 DECLARE @SPName VARCHAR(1000) Declare @moddef nvarchar(max) --Declare Table variables for storing data DECLARE @TStoredProcedures TABLE ( SNo INT IDENTITY(1,1), SPName varchar(max) ) -- text to search for SET @searchFor = '% LinkedServer1%' -- text to replace with SET @replaceWith = ' LinkedServer2’ INSERT INTO @TStoredProcedures(SPName) select distinct object_name(c.id) as SPName from syscomments c, sysobjects o where (c.text like '%' + @ searchFor + '%' and c.text not like '%' + @replaceWith + '%') and c.id = o.id and o.type = 'P' SELECT @count = COUNT(SNo) FROM @TStoredProcedures WHILE (@i <= @count) BEGIN SELECT @SPName = SPName FROM @TStoredProcedures WHERE SNo = @i Set @moddef = (SELECT Replace (REPLACE(definition,@searchFor,@replaceWith) ,'create ','ALTER ') FROM sys.sql_modules a JOIN ( select type, name,object_id from sys.objects b where type in ( 'p' -- procedures ) and is_ms_shipped = 0 )b ON a.object_id=b.object_id where b.name = @SPName) --exec('drop procedure dbo.' + @spname) execute sp_executesql @moddef --select @SPName SELECT @i = @i + 1 END
Using this script, you can change the linked server name which exists in the inside of restored database’s stored procedures. Hope you will find it useful.