Merge 2 database file in to one

[sunt]

Hi Expert,
I am using MS SQL7.0 running on a NT box and there are a database called COLD, this database has 2 physical data files and 2 physical log files. This files are created by someone.

I would like to setup a new database called NEWCOLD, I want to have all data from COLD and put it into this NEWCOLD database but I want this database only maintain a single physical data file and 1 physical log file.

I have tried a couple of ways eg. restore the dump from COLD to the NEWCOLD but it will create 2 data files and 2 log files also.

Is there anyway I can achieve this?
thanks a lot!
Tony

 

[Corran007]

excuse any errors as this is from memory. it should give you a start. log file is easy. you should be able to just clear them out, then delete the unused one? as for the data files. you specifically put where you want the object to be created. just recrate all the objects on the one you want to keep. once the other one is empty you should be able to delete it. look up info on filegroups in bol. should help clear up anything i said that was wrong. and what you need to do.

 

[SQLSister]

I would create the new database and then use DTS to copy the tables from the other two databases, then get rid of the other two databases. If both datbases have the smae structure and you want to combine in one table, when you take the first table's data in you create the table and append the records. Don;t forget to tell it to copy all indexes, constrainst, primary and foreign keys, etc as well as just the structure.

Then for the second db, you only need to append the records to the same table. Now where you may run into problems is with identity keys and duplicate records that won't import because the field requires unique values. To handle the fact that you have duplicate keys with differnet data in both databases, I would probably assign a new key value and add a field to tell me which table the data came from as well as a field for the original key, then when you import the data from subordinate tables you have a way to find the new key value to give those records.

If you have actual duplicate records (not just numeric key duplicates), you will have to determine which if the two records is the correct one. Depending on your database structure you may be able to do this automaitcially (if you have a last updated Date field, for instance) or you may need to copy all the duplicate reords to a holding table and examine them individually to determine which is correct and then only copy the correct one to the final table.

Then of course you need to look at the stored procedures, UDfs and progam code inthe user interface to make sure that things are correctly refernced, particularly if you need to make some changes to make the two databases compatible.

AS you can see combining two databases into one is not a simple or straightforward operation. You need to consider many things.