I have just taken over a project, and need some suggestions on the best and easiest way to reliably determine the schema differences between 2 SQL Server DBs so that I can migrate existing data in a production DB with the schema of a newer and modified version of the same DB on a different server.
FYI: Time and reliability are both critical here, so commercial tools are a possibility if they are the best or only solution.
Here are some details:
1. The production DB is SQL Server 2000, and has tables, views, stored procs, indexes, and constraints. I haven't checked yet, but it might contain triggers and other things, too.
2. The development DB was developed from the same schema as the production DB, but is now in SQL Server 2005. Also, developers who are no longer available made multiple changes (e.g. added new tables; modified some of the existing tables and stored procs; etc.) to the development DB over an extended period of time (and kept no records of what exactly they changed/added).
3. I have new hardware for a new production server, and I need to create a new production DB (in SQL 2005) that uses the schema of the dev DB, but contains the data from the current/old production DB.
4. After step 3 is done, I will need need to do some testing, and then I will need to wipe out the data in the new DB, and re-import the latest data from the current/old production DB into the new DB right before I put the new hardware into production.
Does anyone know of any good way to reliably do this? For example, is there some tool that can reverse-engineer a schema-migration script (after the fact) based on 2 DBs?
Start Free Trial
