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,

1 Answer 1


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 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
      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


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.


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.

  • Thank you Kin, I will try it for sure as soon as I go back to work on Friday, since I'm used to do most things through the gui or the management studio, I'm trying to understand the above T-sql code, can you explain what is it doing before I run it in the production server? I have to replace the actual server name for the current the remote right? I scripted out the current linked server: Commented Dec 12, 2013 at 7:37
  • There are System tables in the default catalog that's associated with this linked server, that I cannot just open a new connection to it too. Commented Dec 12, 2013 at 7:49
  • Not sure how can I attach a copy of the scripted out file Commented Dec 12, 2013 at 7:50
  • EXEC master.dbo.sp_addlinkedserver @server = N'ExcelFacDocReport', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\\Brorporap1\local\Document Control\Reports\FacDocReport.xls', @provstr=N'Excel 5.0' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ExcelFacDocReport',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL Commented Dec 12, 2013 at 7:51
  • I used your code in both servers and I got the same result exactly now for the linked server as I created it there too, now what about the system tables that was linked to in the Catalog then default... it seems that i cannot re-create them there. Commented Dec 12, 2013 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.