Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tanksoldier
Starting Member
4 Posts |
 Posted - 2010-09-03 : 13:06:09
|
| We are decommissioning an older server (2000) and are in the process of migrating all the DBs to a nice new server (2005).One particular database was backed up and restored to the new server, BUT not all applications were re-pointed at the instance on the new server, they were left pointing at the old instance on the old server.Result: We have almost an entire day where some data was inserted into one DB, and some data was inserted into another.I've been handed the task of getting the data that went to the old server onto the new one.I've re-backed up the Db and restored it as a separate instance on the new server and I've been able to compare the two. The "old" instance has about 675 records I need to get onto the new instance.Problem:1. I'm rying to avoid writing a separate query for every single table in the DB, comparing the old and new instances, and inserting. "IS NOT IN" or something similar. I could do that but I'm swamped with work. This project was NOT on my to-do list yesterday, but lots of other stuff was. Anything quicker available?2. Identity: Since the new instance took up where the old instance left off, and the old instance carried on as if nothing had changed, almost every row in every table has PKs duplicated in the other instance, but relating to a different data set.Besides shooting the individual responsible, any suggestions? Is there some way I can use SSIS and Merge Join? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-04 : 05:41:12
|
"Besides shooting the individual responsible, any suggestions? "Yeah, public execution will be good for the soul  Databases that have been migrated should be set to READ ONLY to prevent accidental connections thereafter. Bolting-the-stable-door-after-the-horse-has-gone-though, of course.Are the ID's [of the 675 records added to old database] referenced in other records that have been created? If so you'll need some slightly more complicated queries to sort them out.Do you have CreateDateAndTime column in the Old Database tables - that could be used as a starting point?#Do you only have to transfer newly created records, or do you need to copy changed records too? and then only if not also changed on NEW_DB?If a record exists in Old Database after the cutoff can it be created in new Database? or must there be a check to make sure it wasn't ALSO created in the New Database?If all records created after the cutoff can be created on New Database then my inclination would be:Restore OLD_DB to new server as DB_OLDRestore BACKUP that was used to create the New Database on new server as DB_CUTOFF - this will contain the IDs / PKs so we can establish what was newly created AFTER this point in time (in DB_OLD)Then write a query to transfer records that were different. That's not hard, but may not be in your timescale.At this point you could probably use Red Gate DB Compare to generate a script of Differences between DB_CUTOFF and DB_OLD - and then apply that script to DB_NEW - provided that there are no records potentially changed on both, or you only want Newly Inserted Records from DB_OLD.Failing that I may be able to help in that I have scripts that will generate the script for copying only newer / changed records - which could be adapted to also cross-compare the NEW_DB also. (Red Gate DB Compare may have that sort of 3-way compare/merge tool,??) |
 |
|
|
|
|
|
|
|
Overlapping PKs: merging the data.