I restored a backup of a database on a different SQL Server instance running on the same server and I wanted to compare data in a table found on SQL Server Instance 1 with the same table on SQL Server Instance 2.

 

First add a linked server (all queries will be run from [MyServer\MyInstance1])

exec sp_addlinkedserver @server = 'MyServer\MyInstance2'
go

Then compare the data in the two tables with a except statement:

 

select * from [MyServer\MyInstance1].MyDatabase1.dbo.MyTable1
except
select * from [MyServer\MyInstance2].MyDatabase1.dbo.MyTable1

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.