how to restore a database from different computers into one

Question

I have 3 computers having the same sql server 2005 database, i would like to gather the data from the 3 computers to another computer which has the same database. pls help me tnx in advance

Answer 1

This is called "data conversion" and a lot of your work will be to determine uniqueness on each one of them and coming up with strategies to prevent collisions, mainly primary keys that likely are the same across these databases. No simple answer here, it can be a project in itself.

Answer 2

You need to backup your databases by right clicking in Enterprise Manager and choosing backup before choosing the location etc.

After backing up you can then restore to your local Sql Server by right clicking and choosing restore.

After you have the data locally you will need to write queries to transfer the data to your local database.

Alternatively you can use something like Red Gates Sql Data Compare to compare and transfer data using a visual interface. Although this costs money.

Answer 3

It might be difficult without any manual data transformation. It depends on your database and type of the data. For example what do you use as a keys? If you have sequential integers as a primary/foreign keys, then you will have to do some manual data transformation. IF you use GUIDS, it will get slightly easier, but you still have to ensure that for example some lookup tables doesn't have different guid keys for same items etc.. But there is no took for doing this automatically.

Maybe if you have some very simple data without any relations to other tables (like table with one column with text messages etc) you can script the data with SQL Server Database Publishing Wizard, and then execute the scripts against your target database.

Answer 4

Redgate SQL Toolbelt may be able to help you. You would first copy database to that another computer and then compare it with Sql Data Compare against 3 databases always copying data only one way (to your new database). However I am not 100% sure if it will work like i think it would. You would have to verify it yourself.

Like other people suggested some things like primary keys etc may be problematic.