Sql-server – Help with Transferring Linked Server to another server

sql serversql-server-2005sql-server-2008

Please advice me as I'm trying to move the linked server (Providers with the tables in Catalogs) from SQL server 2005 to another upgraded sqlserver 2008, I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Thanks in advance,

Best Answer

I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Just run the scripted out version (from 2005 servers) on the new sql 2008 server.

You need to manually key-in the passwords as when you script out linked servers, passwords are not scripted out - they are grabbled - ########.

You can check the linked server properties on old server and then match them on the new server using below t-sql :

SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id

Edit:

I know this is old question dated in 2013, but my edit will be useful for future readers ...

If you want to move all or selected Linked servers from one server to another, a better automated option would be to use Copy-LinkedServer (part of dbatools written by Chrissy LeMaire) can be very useful.

e.g.

Copy-LinkedServer -Source YourSourceServerName -Destination YourDestinationServerName -LinkedServers mySQL1, lssvr1 -Force

Remember to remove -LinkedServers parameter if you want to migrate all linked servers from one instance to another.

Related Question