Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    21

    Unanswered: Copying over differential of SQL table

    Hi,

    I have a DB on a SQL server version SQL 2000. I have copied over one table onto a different server into a newly created DB. This DB is on SQL 2005.

    The tables are identical in structure.

    Now I need to copy over the differential from the Source table on the SQL 2000 server into the table on the destination server which is SQL 2005 on a daily basis.

    What is the best way of doing this? It is just 1 table, I do not need the entire DB.

    This table does hold a lot of records and on a daily basis there will be roughly 20,000 that need to be copied over. One of the main reasons for the differential only is that these 2 servers are physically located on different continents so the connection speed is not that great.

    The table in the Source holds about 36 million records already (which I copied over to get the initial start).

    Any ideas, help, suggestions are greatly appreciated.
    Thanks,
    Michiel

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are there datetime stamps on the source data?
    You should be able to set up an SSIS package to copy the modified data from one server to another.
    You could also try replication, but I'm not sure how well that would work across versions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2008
    Posts
    21

    Differential backup

    Hi, I have also looked at a different approach, where I would take the entire 30GB database from one server, create it on the other and then on a daily basis run a differential backup on the source DB and move this file and restore it into the destination DB.

    While trying this though I was surprised about the size of the differential backup. The entire db is 30GB, and it was backuped last night (about 18 hours ago). Then I ran the differential backup and the size of that file came out to be 3GB (this is roughly 10% of the DB itself). Honestly, I was expecting the differential backup file to be maybe 75MB at most.

    Any ideas on why this file was so big?
    Thanks,
    Michiel

  4. #4
    Join Date
    Jan 2008
    Posts
    21
    Thanks Blindman, I have been playing with the SSIS package but still seems to be pretty time consuming accross the ocean.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If the table acquires only INSERTs, then you can easily set up a delta transfer. Nightly extract the rows for the day into a text file, then FTP it to a site accessible by the agent account on the other side, and from there FTP it to a local folder, and then BCP it into the target table.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    You could also try replication, but I'm not sure how well that would work across virgins.
    Don't know, but I'd like to watch
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Quote Originally Posted by mvanmeurs
    ....and the size of that file came out to be 3GB (this is roughly 10% of the DB itself). Honestly, I was expecting the differential backup file to be maybe 75MB at most.

    Any ideas on why this file was so big?
    differential backup works at extent level and copy all the extents modified after last full backup. so if u have mainly inserts (in pk order) u can expect a smaller size of diff-backup. if u have frequent updates the diff-backup size can go bigger.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Quote Originally Posted by blindman
    You could also try replication, but I'm not sure how well that would work across virgins.
    Don't know, but I'd lick a witch
    Look. I can't change quotes too!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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