Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Moving subsets of data between servers

    I have a fairly complicated SQL Server database (around 300+) tables and I need to move portions of those tables to another SQL Server database instance.

    My client has all his customer's data in one big database. One of those customers decided that for security reasons, they wanted their database onsite. Now I have to move all the data, for just that customer, to another database. This customer's data is spread all over the 300+ tables and it's fairly normalized so lots of parent-child relationships, etc...

    Has anyone ever tried to do something like this? Do I just have to write scripts/sql to do it manually? I was hoping for something easier than just writing all the sql.

    Cheers,
    Justin

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I think you are going to need to write scripts based on what field value identifies that customer. Based on the amount of data, I would extract the schema, create it on the new server and write bcp scripts to bulk out/in the data, then create whatever idxs and constraints on the tables post data migration. I guess there will be some tables where you need all the data (dimension and/or lookup tables), etc.

  3. #3
    Join Date
    Dec 2009
    Posts
    1

    Try a data migration tool

    Check out relationalmigration.com
    I don't know how well it performs with HUGE data sets, but it works well for us. We use it to move configuration data from a development environment to production.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by gtylar View Post
    Check out relationalmigration.com
    I don't know how well it performs with HUGE data sets, but it works well for us. We use it to move configuration data from a development environment to production.
    relationalmigration.com does not seem to be up.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you your foreign keys set to cascade on delete, then why not take a copy of the database and then delete all other customers?
    George
    Home | Blog

  6. #6
    Join Date
    Dec 2009
    Posts
    2

    RE: Relational Migration

    At a first glance, Relational Migration looks promising. It does a whole lot of other stuff but it seems to support subset copying as well.

    I'm going to give it a try. I'll post any results a little later.

    Cheers.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •