Is there a way to compare 2 databases for differences in records etc
Scenario:
mydatabase.mbd has been restored to \\server\data\Company\SQL\
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
mydatabase.mbd has been restored to \\server\data\Company\SQL\
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
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
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?
Are these Access dbs linked to sql server or something?
ASKER
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.
Does this make sense? Sorry its a bit tricky to explain.
ASKER
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?
"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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Have I done this correctly as please have a look.
Yellow - Edit to suite my DB
Please see error by debug msg.
SQLTSQLError.png
@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.
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.
ASKER
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
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
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
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
Friend you have to write actual table names from the database
ASKER
@ 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?
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?
ASKER
@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
"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
We already explained why, read all posts again.
ASKER
Because I have to use the actual name of my table? in the query instead where is says .tABLE correct?
yes. if you don't know how to do that then show a screenshot of one of your tables with columns
ASKER
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?