Post reply

Moving tables from one database to another database

  • November 3, 2011 at 12:01 pm

    #390757

    we are using sql server 2005,actually frequently we have to restore production database to acceptance environment ,sometimes database in acceptance have more tables than prod database. in that case i just use import\export wizard or select into command to transfer extra tables in acceptance to a temporary created database in same server, but while doing that size of the table in temp database is less than actual one.So ,while looking table structure there is no index’s,primary keys,foreign keys etc. could ypu please post your suggestion

  • November 3, 2011 at 1:06 pm

    #1402891

    IIRC, the Import/Export Wizard only move data. It will contruct the basic table but does not create any of the indexes, foreign keys, etc.

  • November 3, 2011 at 1:06 pm

    #1402892

    you'll want to script the table out from the source, including indexes, and create it on the destination database.

    then you can use the import/export wizard,and change the step that says drop and recreate the destination table, so that it leaves your table definition in place.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 3, 2011 at 1:18 pm

    #1402899

    actually i did it,but it had some foreign keys,so i have to move that tables also....

  • November 3, 2011 at 9:48 pm

    #1403041

    You can backup the production database and restore it in the acceptance environment.

    Since you need to do this regularly better to have a restore DB script. This script should take the latest backup from the Production DB( which will be a scheduled backup) from a comman path and restore the DB to the acceptance environment.

    Create a job and call this script. Whenever you need a restore, run this job and ensure the DB is restored in the acceptance environment.

    Hope this helped you.

  • November 4, 2011 at 6:24 am

    #1403163

    if you dont mind could you explain clearly..?

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

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