[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

Is there a way to compare 2 databases for differences in records etc

Scenario:

mydatabase.mbd has been restored to \\server\data\Company\SQL\Backup_Restore due to the local mydatabase.mbd being corrupt. The problem at hand how ever is that the restored DB  might not have all the "records" and now this needs to be verified that indeed the restored db and the local on is in fact 1:1 copies and no records are missing.

So that is my problem I need to cross ref mydatbase.mdb"Local" with mydatbase.mdb "Restored backup"
and then some how perhaps if there are differences merge the differences perhaps to a new db so that the new db and corrupt one will be 1:1 copies

See this link as you do get software but is there nothing freeware out there to do this?
http://www.fmsinc.com/MicrosoftAccess/DatabaseCompare.html

I somehow get the feeling I could do this using innerjoints and outterjoint TSQL but I haven't done this in year and anyone who can help me resolve this is a legend
0
benthomas
Asked:
benthomas
  • 11
  • 6
  • 3
  • +1
4 Solutions
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can use EXCEPT in SQL which will help you find the rows which are there in the Local but not in the Restored Database.

INSERT INTO Restored backup.DBO.tABLE


SELECT * FROM Local.DBO.tABLE

EXCEPT

SELECT * FROM Restored backup.DBO.tABLE


This Query will insert the records to the Restored table which are there in the local db but not in the restored on

You have to run this for each table separately or you can also make loop for dynamic using sys.tables so that you can do it for all tables at a go

Hope this will help
0
 
nemws1Commented:
I usually prefer a LEFT JOIN for this:

SELECT loc.id
FROM Local.DBO.tABLE AS loc
    LEFT JOIN Restored backup.DBO.tABLE AS res
        ON loc.id = res.id
WHERE res.id IS NULL

Open in new window


This will give you all the 'id's for entries in the local table that aren't in the remote table.
0
 
prequel_serverCommented:
like all technical problem, it depends. How big is the job and how sever is the downtime?

What caused the corruption in the first place? records being wrongfully inserted/updated or some weird file level errors?

How many tables and other objects do you have?
-finding the difference between both dbs could be very labor intensive if you choose the manual approach suggested above.

In that case, buying a commercial product like the one you found for $200 might be very worth it.

If I was in your shoes I would probably buy something or use a trial to make sure it works as expected.

here are a few I know about and I believe are free to an extent:

http://www.starinix.com/sqlcompare02.htm
http://www.accdbmerge.net/home
http://www.softinterface.com/Compare-Database/Compare-Access.HTM

good luck
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
benthomasAuthor Commented:
Trying all of these out now and will get back to you today.
Just out of interest sake is there a way to forward this topic form once EE user to another EE user with different login credentials?
0
 
benthomasAuthor Commented:
Directed to Nemws1:

if my restored db is called Supdata_Test and my original database is called SupData_Original and they both listed in SQL management studio then how do I run the LEFT JOIN to pickup all differences in SupData_Test and SupDataOriginal and then Merging all the latest differences between both DB's to a new DB called SupData_Merge.

(i.e. I need to check both db's as the one was corrupted and now I restored a backup. Now what I want to do is check differences between the corrupted(latest DB) and the restored DB and then create a new db which has all the data and merge data so that the "New DB" is 100% up to date with all the records etc before the other DB crashed.
0
 
benthomasAuthor Commented:
Directed to prequel_server:

OK I have ran and installed both of the above software but when I try to run a compare between them I get the following error (See attachment)
CompatibleError1.png
0
 
prequel_serverCommented:
I'm confused. I thought you're trying to find the delta of two MS Access dbs, not sql server. Can you clarify?
Are these Access dbs linked to sql server or something?
0
 
benthomasAuthor Commented:
No what I have done was the "corrupted MDB file" I imported into a New SQL DB called SupData-Test and then the "restored MDB file" I imported into a NEW SQL DB called SupData-Original.Now what I want to do is run a query that will first off ditermine the different and latest records between the two dbs and then once I find out the latest diferences I want to create a new DB called SupData-Merge which will have not only the "Latest merge difference records" but also all the records in both db's i.e. I want to get the DB back to the stable state it was before it corrupted.

Does this make sense? Sorry its a bit tricky to explain.
0
 
benthomasAuthor Commented:
Directed @ Vikas Garg:

"You have to run this for each table separately or you can also make loop for dynamic using sys.tables so that you can do it for all tables at a go:

How do I do this?
0
 
prequel_serverCommented:
You're end goal is to have a new mdb that is "current/complete/having all missing records" but a not corrupted. Correct?

-The software I referred you to, including the link you found is for MS Access dbs. It seems like you imported both mbd's into sql server and then tried to run the software which is very flawed.  The software samples I suggested was for your "source" corrupt and recovered MS Access dbs. If you wanted delta in Sql server I would suggest different tools.

Here's what I suggest:

1. Use one of the software using the actual mdb files. This will find all objects and record differences across all tables for you. For example if you had a table in one db but not the  other or if there are records in one table that are missing in the other.

2. If you are not satisfied with this approach and you want to run things manually in Sql Mgmt Studio (which I don't recommended) . You can try the following pseudo script in the Master db context, and ideal if you're in sa account to avoid any perm issues.

 SELECT *
FROM [SupData_Original].dbo.TABLE1

    LEFT JOIN [Supdata_Test].dbo.TABLE1
        ON TABLE1.id = TABLE1.id

WHERE [Supdata_Test].[TABLE1].id IS NULL

This will give you the delta records across both sql dbs only for Table1, where Table1 is the first common table in both dbs.  You would have to run this for each common table. Do this across all tables dynamically you need to write dynamic T-SQL which is out of scope of this question.

Then even after you find the delta records you need to apply them in the recovered Mdb file which will be a pain unless you do a linked tables approach perhaps?

try the software first before you get into all this mess.
0
 
benthomasAuthor Commented:
Directed @ NEWS1:

Have I done this correctly as please have a look.
Yellow - Edit to suite my DB
Please see error by debug msg.
SQLTSQLError.png
0
 
prequel_serverCommented:
@benthomas

Given that you didn't modify News1's query for your needs at all, it tells that you have very limited knowledge in T-SQL.

We gave you general sql to apply in your environment. We have no idea what your db looks like. If you can't modify it then give us a sample table name you have in your SupData_Test and give us the unique ID/PK column of this table.
0
 
benthomasAuthor Commented:
Directed @ Vikas :
You mentioned I should use this TSQL too
"Hi,

You can use EXCEPT in SQL which will help you find the rows which are there in the Local but not in the Restored Database.

INSERT INTO Restored backup.DBO.tABLE


SELECT * FROM Local.DBO.tABLE

EXCEPT

SELECT * FROM Restored backup.DBO.tABLE
"

I tried it but look at the errors and please assists if you can.
TSQLError3.png
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Since you told that you have all the tables are only record mismatch is your concern so you have to compare each table for EXCEPT to insert the missed records
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Friend you have to write actual table names from the database
0
 
benthomasAuthor Commented:
@ prequel_server

Yes well done that's correct I am still very much a neewb for TSQL .
So all I need to to take the difference from Original-DB(Tables and all) and Restore-DB Tables and all) and then apply the latest changes to Restore DB (Tables and all) so that the DB is back to the current state before it was corrupted.

That's the goal I am looking to achieve
And if you can help me archive this perhaps I can learn through this and in time be a intermediate TSQL neewb?
0
 
benthomasAuthor Commented:
Even when running the TSQL script I get this error  can anyone explain why?
TSQL4.png
0
 
benthomasAuthor Commented:
@Vikas Garg

"Since you told that you have all the tables are only record mismatch is your concern so you have to compare each table for EXCEPT to insert the missed records"

OK I will run each query on each table , so correct me if I am wrong do I select the table in the database and then run the TSQLQuery? and If so can you help me write the query here?

Thanks for your time and patience
0
 
prequel_serverCommented:
We already explained why, read all posts again.
0
 
benthomasAuthor Commented:
Because I have to use the actual name of my table? in the query instead where is says .tABLE correct?
0
 
prequel_serverCommented:
yes. if you don't know how to do that then show a screenshot of one of your tables with columns
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now