Sql-server – views restored from backup in a different server referring to linked servers

linked-serverrestoresql serversql-server-2019view

I have 3 SQL servers X, Y, Z and 2 databases X.A and Y.B.
There are (many) view in X.A that uses 4-level naming in their definition:

SELECT ... FROM Y.B.SCHEMA.TABLE

From this I infer that X contains a linked server definition for Y.
I am restoring X.A and Y.Bfrom backup files into server Z, so I now have
Z.A and Z.B available in the same server.
But these views obviously fail in Z due to not knowing what Y is.

I would like these views to work in the restored database Z.A, in such a way that they get the data from Z.B (and not from Y.B), without having to change the views (there are many such views). I am not sure if this is possible. I am able to do changes on server Z as needed, but I do not have any access to X and Y. The users of the view also only have access to Z.

I thought about defining a synonym Y in Z to point to localhost but it seems I need to refer at least to a schema or schema.table when creating a new synonym. Or can I have a synonym of just the server name?

I also thought about defining a linked server named Y in Z, but pointing to itself. Here I was only able to make a linked server named localhost pointing to itself, and this does not help solve my problem.

Is this at all possible? Can I convince SQL server to use Z when Y is referred to in the view definition?

Best Answer

Synonyms will not be useful for your need since that will require changing the view definition to specify 2-part names.

I also thought about defining a linked server named Y in Z, but pointing to itself. Here I was only able to make a linked server named localhost pointing to itself, and this does not help solve my problem.

It is possible to create a loopback linked server with the name Y using SSMS by selecting "Other data source" (instead of SQL Server) and then specifying a SQL Server provider. This can also be done via T-SQL:

EXEC master.dbo.sp_addlinkedserver 
      @server = N'Y'
    , @srvproduct=N''
    , @provider=N'SQLNCLI11'
    , @datasrc=N'.'
    , @catalog=N'YourDatabase';

You'll need to also configure the linked server security context according to your requirements.

Related Question