Script All Linked Servers. Run on source server, the copy and paste message window into connection for target server. Passwords are hashed.

Script Linked Servers.sql

SET NOCOUNT ON;

/* Local Vars */
DECLARE @RowID_Server  INT
		,@RowID_Login  INT
		,@RowID_Option INT
		,@NBSP		   CHAR(1) = CHAR(10);

/* Results for linked servers and currently configured options */
DECLARE @LinkedServers TABLE
(
	RowID_Server								  INT IDENTITY(1, 1)
	,server_id									  INT
	,name										  VARCHAR(4000)
	,product									  VARCHAR(4000)
	,provider									  VARCHAR(4000)
	,data_source								  VARCHAR(4000)
	,location									  VARCHAR(4000)
	,provider_string							  VARCHAR(4000)
	,catalog									  VARCHAR(4000)
	,connect_timeout							  VARCHAR(4000)
	,query_timeout								  VARCHAR(4000)
	,is_linked									  VARCHAR(4000)
	,is_remote_login_enabled					  VARCHAR(4000)
	,is_rpc_out_enabled							  VARCHAR(4000)
	,is_data_access_enabled						  VARCHAR(4000)
	,is_collation_compatible					  VARCHAR(4000)
	,uses_remote_collation						  VARCHAR(4000)
	,collation_name								  VARCHAR(4000)
	,lazy_schema_validation						  VARCHAR(4000)
	,is_system									  VARCHAR(4000)
	,is_publisher								  VARCHAR(4000)
	,is_subscriber								  VARCHAR(4000)
	,is_distributor								  VARCHAR(4000)
	,is_remote_proc_transaction_promotion_enabled VARCHAR(4000)
);

/* Get linked server info */
INSERT INTO @LinkedServers
(
	server_id
	,name
	,product
	,provider
	,data_source
	,location
	,provider_string
	,catalog
	,connect_timeout
	,query_timeout
	,is_linked
	,is_remote_login_enabled
	,is_rpc_out_enabled
	,is_data_access_enabled
	,is_collation_compatible
	,uses_remote_collation
	,collation_name
	,lazy_schema_validation
	,is_system
	,is_publisher
	,is_subscriber
	,is_distributor
	,is_remote_proc_transaction_promotion_enabled
)
SELECT	ss.server_id
		,ss.name
		,ss.product
		,ss.provider
		,ss.data_source
		,ss.location
		,ss.provider_string
		,ss.catalog
		,ss.connect_timeout
		,ss.query_timeout
		,ss.is_linked
		,ss.is_remote_login_enabled
		,ss.is_rpc_out_enabled
		,ss.is_data_access_enabled
		,ss.is_collation_compatible
		,ss.uses_remote_collation
		,ss.collation_name
		,ss.lazy_schema_validation
		,ss.is_system
		,ss.is_publisher
		,ss.is_subscriber
		,ss.is_distributor
		,ss.is_remote_proc_transaction_promotion_enabled
FROM	sys.servers AS ss
WHERE	ss.is_linked = 1;

/* Trap number of linked servers to process */
SELECT @RowID_Server  = @@ROWCOUNT;

/* While servers exist to process, do the roar */
WHILE @RowID_Server > 0
BEGIN
	DECLARE @WorkingServerName			 sysname
			,@WorkingLinkedServerCommand VARCHAR(MAX);

	/* Build addlinkedserver command */
	SELECT	@WorkingServerName = ls.name
			,@WorkingLinkedServerCommand = 'EXEC master.dbo.sp_addlinkedserver @server = ''' + ls.name
										   + '''
								   ,@srvproduct = ''' + ls.product + '''
								   ,@provider = ''' + ls.provider + ''''
										   + CASE
												 WHEN ls.provider_string IS NULL THEN ''
												 ELSE ',@provstr = ''' + ls.provider_string + ''''
											 END + @NBSP + 'GO' + @NBSP + @NBSP
	FROM	@LinkedServers AS ls
	WHERE	ls.RowID_Server = @RowID_Server;

	/* Drop temp table if exists */
	DROP TABLE IF EXISTS #RemoteLogin;

	/* Capture mapped logins for linked server */
	CREATE TABLE #RemoteLogin
	(
		RowID_Login	  INT	  IDENTITY(1, 1)
		,LinkedServer sysname
		,LocalLogin	  sysname NULL
		,SelfMapping  BIT
		,RemoteLogin  sysname NULL
	);

	INSERT INTO #RemoteLogin
	(
		LinkedServer
		,LocalLogin
		,SelfMapping
		,RemoteLogin
	)
	EXEC master.dbo.sp_helplinkedsrvlogin @rmtsrvname = @WorkingServerName;

	/* Capture count of mapped logins for working server */
	SELECT @RowID_Login	 = @@ROWCOUNT;

	/* While logins exist to map, do the inner-roar */
	WHILE @RowID_Login > 0
	BEGIN
		/* Passwords are encrypted, and thus hashed. */
		SELECT	@WorkingLinkedServerCommand = @WorkingLinkedServerCommand
											  + 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = '''
											  + rl.LinkedServer + '''
									 ,@useself = ''' + CASE
														   WHEN rl.SelfMapping = 1 THEN 'true'
														   ELSE 'false'
													   END + '''
									 ,@locallogin = ' + CASE
															WHEN rl.LocalLogin IS NULL THEN 'NULL'
															ELSE '''' + rl.LocalLogin + ''''
														END + '
									 ,@rmtuser = ' + CASE
														 WHEN rl.RemoteLogin IS NULL THEN 'NULL'
														 ELSE '''' + rl.RemoteLogin + ''''
													 END + '
									 ,@rmtpassword = '
											  + CASE
													WHEN rl.RemoteLogin IS NULL THEN 'NULL'
													ELSE '''########'' /* Password is encrypted, thus hashed */'
												END + @NBSP + 'GO' + @NBSP + @NBSP
		FROM	#RemoteLogin AS rl
		WHERE	rl.RowID_Login = @RowID_Login;

		SET @RowID_Login = @RowID_Login - 1;
	END;

	/* Drop table #RemoteLogin */
	DROP TABLE #RemoteLogin;

	/* Drop temp table if exists */
	DROP TABLE IF EXISTS #LinkServerOptions;

	/* Temp table to unpivot server options and handle them one by one */
	CREATE TABLE #LinkServerOptions
	(
		RowID_Option INT IDENTITY(1, 1)
		,KeyColumn	 VARCHAR(4000)
		,KeyValue	 VARCHAR(4000)
	);

	/* Insert list of server options for current linked server and translate returned data in available/usable options with CASE statement */
	INSERT INTO #LinkServerOptions
	(
		KeyValue
		,KeyColumn
	)
	SELECT	up.Vals
			,CASE up.Options
				 WHEN 'query_timeout' THEN 'query timeout'
				 WHEN 'connect_timeout' THEN 'connect timeout'
				 WHEN 'is_collation_compatible' THEN 'collation compatible'
				 WHEN 'collation_name' THEN 'collation name'
				 WHEN 'is_data_access_enabled' THEN 'data access'
				 WHEN 'is_distributor' THEN 'dist'
				 WHEN 'is_publisher' THEN 'pub'
				 WHEN 'is_subscriber' THEN 'sub'
				 WHEN 'is_remote_login_enabled' THEN 'rpc'
				 WHEN 'is_rpc_out_enabled' THEN 'rpc out'
				 WHEN 'lazy_schema_validation' THEN 'lazy schema validation'
				 WHEN 'is_system' THEN 'system'
				 WHEN 'uses_remote_collation' THEN 'use remote collation'
				 WHEN 'is_remote_proc_transaction_promotion_enabled' THEN 'remote proc transaction promotion'
			 END
	FROM	(
				SELECT	ISNULL(connect_timeout, 'NULL') AS connect_timeout
						,ISNULL(query_timeout, 'NULL') AS query_timeout
						,ISNULL(is_linked, 'NULL') AS is_linked
						,ISNULL(is_remote_login_enabled, 'NULL') AS is_remote_login_enabled
						,ISNULL(is_rpc_out_enabled, 'NULL') AS is_rpc_out_enabled
						,ISNULL(is_data_access_enabled, 'NULL') AS is_data_access_enabled
						,ISNULL(is_collation_compatible, 'NULL') AS is_collation_compatible
						,ISNULL(uses_remote_collation, 'NULL') AS uses_remote_collation
						,ISNULL(collation_name, 'NULL') AS collation_name
						,ISNULL(lazy_schema_validation, 'NULL') AS lazy_schema_validation
						,ISNULL(is_system, 'NULL') AS is_system
						,ISNULL(is_publisher, 'NULL') AS is_publisher
						,ISNULL(is_subscriber, 'NULL') AS is_subscriber
						,ISNULL(is_distributor, 'NULL') AS is_distributor
						,ISNULL(is_remote_proc_transaction_promotion_enabled, 'NULL') AS is_remote_proc_transaction_promotion_enabled
				FROM	@LinkedServers
				WHERE	name = @WorkingServerName
			) AS ls
		UNPIVOT (
					Vals
					FOR Options IN (query_timeout, connect_timeout, is_collation_compatible, collation_name
									,is_data_access_enabled, is_distributor, is_publisher, is_subscriber
									,is_remote_login_enabled, is_rpc_out_enabled, lazy_schema_validation, is_system
									,uses_remote_collation, is_remote_proc_transaction_promotion_enabled
								   )
				) AS up;

	/* Capture count of server options for working server */
	SELECT @RowID_Option  = @@ROWCOUNT;

	/* While linked server options exist to process, do the inner-roar */
	WHILE @RowID_Option > 0
	BEGIN
		DECLARE @WorkingOptionCommand VARCHAR(MAX) = '';

		/* String builder for each option of working linked server */
		SELECT	@WorkingLinkedServerCommand = @WorkingLinkedServerCommand
											  + 'EXEC master.dbo.sp_serveroption @server = ''' + @WorkingServerName
											  + '''
								,@optname = ''' + lso.KeyColumn + '''
								,'			  + '@optvalue = '
											  + CASE
													WHEN lso.KeyValue = 'NULL' THEN lso.KeyValue /* Pass string null as literal NULL value, thereby escaping string handling */
													ELSE '''' + CASE
																	WHEN lso.KeyColumn NOT LIKE '%timeout%'
																		 AND   lso.KeyValue = '1' THEN 'true'
																	WHEN lso.KeyColumn NOT LIKE '%timeout%'
																		 AND   lso.KeyValue = '0' THEN 'false'
																	ELSE lso.KeyValue
																END + ''''
												END + @NBSP + 'GO' + @NBSP + @NBSP
		FROM	#LinkServerOptions AS lso
		WHERE	lso.RowID_Option = @RowID_Option;

		SET @RowID_Option = @RowID_Option - 1;
	END;

	DROP TABLE #LinkServerOptions;

	/* Output script options as text in message window */
	PRINT '/*****************************************/';
	PRINT '/** Linked Server ' + @WorkingServerName + ' **/';
	PRINT '/*****************************************/';
	PRINT '';
	PRINT @WorkingLinkedServerCommand;
	PRINT @WorkingOptionCommand;

	SET @RowID_Server = @RowID_Server - 1;
END;