SQL Server 2005 script with join across Database Servers
- by Robin Day
I have the following script which I use to give me a simple "diff" between tables on two different databases. (Note: In reality my comparison is on a lot more than just an ID)
SELECT
MyTableA.MyId,
MyTableB.MyId
FROM
MyDataBaseA..MyTable MyTableA
FULL OUTER JOIN
MyDataBaseB..MyTable MyTableB
ON
MyTableA.MyId = MyTableB.MyId
WHERE
MyTableA.MyId IS NULL
OR
MyTableB.MyId IS NULL
I now need to run this script on two databases that exist on different servers. At the moment my solution is to backup the database from one server, restore it to the other and then run the script.
I'm pretty sure this is possible, however, is this likely to be a can of worms? This is a very rare task I need to perform and if it involves a large number of DB setting changes then I will probably stick to my backup method.