Knowledgebase

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