none
Linked Servers migration RRS feed

  • Question

  • Hi,

    I am in process of migrating one single user databases from SQL 2005 (SP4) to SQL 2012 (SP2). 

    • SQL 2005 Instance has 4 linked servers.
    • SVR_Datasource are different SQL 2005 instances

    How to I know?

    • My database is using any linked servers (Out of 4).
    • In General, Is there any way to track what all users databases are taking part of linked servers.

    Thanks

    Tuesday, February 24, 2015 10:29 AM

Answers

All replies

  • You can find reference to the link servers by using Aaron's great procedure

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 24, 2015 11:47 AM
    Moderator

  • Linked server are not database objects but server objects its not. I would say just script out and create it on destination

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Tuesday, February 24, 2015 3:16 PM
    Moderator
  • Try this

    DECLARE @command varchar(1000) 
    SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
    SELECT ''?'', OBJECT_NAME(id) as ROUTINE_CATALOG  FROM syscomments  WHERE [text] LIKE ''%LinkedServerName%'' AND OBJECTPROPERTY(id, ''IsProcedure'') = 1 GROUP BY OBJECT_NAME(id)
    END' 
    EXEC sp_MSforeachdb @command

    OR

    DECLARE @command varchar(1000) 
    SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
    SELECT ''?'',ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%SearchString%'' AND (ROUTINE_TYPE=''PROCEDURE'' or ROUTINE_TYPE=''FUNCTION''); 
    END' 
    EXEC sp_MSforeachdb @command


    --Prashanth



    Tuesday, February 24, 2015 3:22 PM
    Answerer