Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    19

    Unanswered: Migrating Many Linked Servers to Targetserver

    Hi All,

    I have to migrate 25 linked servers from one server to another server along with databases is their easiest way to copy these linked servers at a time using DTS\SSIS or any best practice.

    A quick response on this is highly appreciated.

    Thanks
    Nageswara Lakka
    Last edited by nlakka; 04-17-09 at 16:27.

  2. #2
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    a simple method, right click on the linked server from studio manager, select script linked server as create to new query window, then do a create to clipboard for the others and paste them into the same query window.

    Or you can write an sql script using the sysservers system table which has the information for linked servers and parse to an insert script for your other server.
    set nocount on
    select 'exec master..sp_addlinkedserver
    @server = ''' + srvname + ''',
    @srvproduct =', + '''' + srvproduct + ''',
    @provider =', + '''' + providername + ''', @datasrc = ' , '''' +
    datasource + '''' from sysservers

    check logins when finished and test

    Or here is a simple script for a linked server that you can use


    U can use this script and replace and repeat for each linked server
    /****** Object: LinkedServer [home\sqlhome] Script Date: 04/19/2009 11:50:12 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'home\sqlhome', @srvproduct=N'SQL Server'
    /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'home\sqlhome',@useself=N'False',@loc allogin=NULL,@rmtuser=N'joeuser',@rmtpassword='joe password'

    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'collation compatible', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'rpc', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'rpc out', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'dpub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'collation name', @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'home\sqlhome', @optname=N'use remote collation'

    Just a thought if you are migrating 25 databases and logins, I am wondering why you don't make a replica of the instance and remove what you don't want.
    Last edited by burkular; 04-19-09 at 12:57.

  3. #3
    Join Date
    Jun 2004
    Posts
    19
    Hi Burkular,

    It was a requirement from my client and i know that i can script or as well i can write script mentioned by you, since there was multilple RDBMS linked servers are created and they want to migrate the same as it is. If i start creating one by one i can not complete the task in the given time, this is not for one server requirement it is for many and may come in future.

    I feel while creating scripts mentioned by you i am unable to fetch Passwords.

    I am looking some thing like DTS transfer or SSIS....

    thanks again.
    Nageswara Lakka
    Last edited by nlakka; 04-20-09 at 14:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •