Post reply

Data Migration

  • November 29, 2005 at 9:47 pm

    #95959

    First things first, I apologize if this isn't the most appropriate area for this post...

    I have a production server and a test server.  The production server has one giant database that's being used by 2 applications.  My goal was to break that database into 2 separate databases, one for each application.

    About a month ago, I made the necessary changes on the test server.  I started with a copy of the (giant) production database.  Then I created a second database, and began moving database objects.

    At this point, my production server has the original database (DB_Original), and my test server has the 2 new databases (DB_Original, and DB_New).

    I need to update the tables on the test server with the production data, but I need to keep the permissions of the objects on the test server since some of them have changed.  Also, I'm concerned about maintaining referential integrity between related tables.  Once I've sync'd the data, I believe I can just attach the 2 new databases to the production server to finish my changes.

    What is the best way to copy the production data to the test tables without losing permissions on the test tables and while maintaining referential integrity between related tables?

    Thanks in advance for any help.

  • December 1, 2005 at 1:37 am

    #606957

    1. clear out test tables of all data.

    2. establish building block tables...ie the parent tables in any parent-child relationships.

    3. dts the data over from oldserver to newserver in the order of the table list generated by step 2.

    or create multiple

    insert into newserver.newdb.dbo.newtable

    select * from oldserver.olddb.dbo.oldtable

    style scripts and run in sequence.

    this way you should not have 'loss of permissions issues' as you are not dropping any tables.  Beware of potential user/password synchronisation issues however.

    be careful....and good luck!

  • December 1, 2005 at 3:32 am

    #606975

    Carry on the rest of the discussion here in the cross post..

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=7&messageid=240605

    If you wish to cross post, please be aware that the daily email lists new messages in all forums anyway.

    Cheers

  • December 2, 2005 at 11:01 am

    #607447

    Thanks for the replies.  I have since been working on some DTS packages that will accomplish what I need.

    One question that I have come up with since I've been reading a bit more about replication is ... how simple (or not) is it to setup replication on my production server and push the data objects and data to a test database?

    In my case, I would obviously not be concerned with synchronizing any changes back to the publisher.  In fact, I would not want the publisher's data (my production database) to ever be affected by the subscriber.  I would simply like to be able to periodically sync the test database with the live database.

    Can I do this easily without affecting my production database?

    Thanks again for the replies.

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

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