Question

How can I restore my old data into the new design in SQL Server 2000

SQL Server 2000, SQL database, SQL Server

The SQL Server 2000 application I was using was badly designed. Now that there has been a rework of my database design, so many more tables and columns are being added. How can I restore my old data into the new design?

There is no way to directly restore data from one database design to another. Restoring data typically refers to taking a database backup and loading that backup into the SQL Server so that the database looks like it did at a prior point in time.

In order to load for data from the existing schema into your new schema you'll need to use DTS or SSIS to move the data from each of the old tables into the new table, mapping each column into it's new location. Some tables from the source schema will need to be split into two or more tables in the new schema (normalized) while some other tables may be combined from two or more tables in the old schema into a single table in the new schema (denormalized).

Whom ever designed the new schema should have a pretty good idea of what the differences between the two schemas are, and they should be able to do the bulk of the work setting up the DTS / SSIS package between the two schemas.