need to copy 100 tables from 1 server/db to another server/db..

Question

Post reply

need to copy 100 tables from 1 server/db to another server/db..

s1123

Mr or Mrs. 500

Points: 567
More actions
February 27, 2012 at 10:27 pm

#269214

hi
i want to copy only 100 tables data from the db which have 200 tables
what is the best way?
and
what is the easy way?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

you could use, SSIS, Import Export Data Wizard, BCP.

Quickest would be the wizard but if you want to do fancy things with the data on the copy then SSIS

Brandie Tarvin SSC Guru Points: 173075 More actions · Answer 2

The easiest way?

I'd say restore the database to the other server and delete the unneeded tables. Which runs into problems if you delete FK related tables in the wrong order.

Do you just need the data in the tables or do you need the schema (such as indexes, identities, etc?)

Using SSIS to do the move will cause problems because SSIS always sends over the schema bits in the wrong order, then errors out because it can't find a dependency. At least, in my experience I've never gotten it to work.

If you just need the data, then a basic Import / Export wizard will work. If you need the schema, you generate the scripts on the tables by right clicking the database and going to Tasks -> Generate Scripts and walk through the screens, only picking the tables you need and marking which properties and other related objects are necessary. After you create the script, port it over to the new server. Then do the Import / Export wizard, making sure to edit the mappings on all the tables so that Identity Insert is on (if you need it).

But that's more complicated then restoring the db and deleting the unnecessary tables. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Narud SSCrazy Points: 2837 More actions · Answer 3

Just yesterday in the SQL Server Central newsletter there is an article of Phil Factor talking about how to automate the scripts generation to migrate tables from one server to other with Powershell.

http://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/

I hope that this could be helpful for you.

Best regards!

Brandie Tarvin SSC Guru Points: 173075 More actions · Answer 4

I just figured out that this is the guy who wants the answers to interview questions. He's not going to respond to this thread because he's not actually doing any of this himself. He just wants everyone's answers so he can get a job.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.