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.
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),
-- 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)
SELECT @SPName = SPName FROM @TStoredProcedures WHERE SNo = @i
Set @moddef =
Replace (REPLACE(definition,@searchFor,@replaceWith) ,'create ','ALTER ')
FROM sys.sql_modules a
( select type, name,object_id
from sys.objects b
where type in (
'p' -- procedures
and is_ms_shipped = 0
ON a.object_id=b.object_id where b.name = @SPName)
--exec('drop procedure dbo.' + @spname)
execute sp_executesql @moddef
SELECT @i = @i + 1
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.