Post reply

Database Migration.

  • June 27, 2017 at 8:48 am

    #337904

    I need to migrate one of the database we have from one server (2008 standard ) to another server (2014 standard) and I have thought about restoring the database but the problem which I am running into is that users have been updating both databases for quite some time now. My question is if there is a tool or a magic wand I can use to make sure that the restore won't overwrite the existing data.Has anyone worked on this sort of issue and how it was resolved? Any help is highly appreciated.

  • June 27, 2017 at 8:54 am

    #1948396

    Can you restore it under a new name?

  • June 27, 2017 at 8:58 am

    #1948397

    Unfortunately no.

  • June 27, 2017 at 9:03 am

    #1948400

    There is no magic wand. Either you need to get agreement that one of the databases is the chosen one and lose all the updates in the other, or you need to work out how to resolve this issue.

    Such resolution is likely to require significant investigation (to understand exactly which tables have been updated and the relationships between these tables) and development (of a solution which does a bidirectional merge of 'new' data).

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 27, 2017 at 7:23 pm

    #1948482

    newdba2017 - Tuesday, June 27, 2017 8:48 AM

    I need to migrate one of the database we have from one server (2008 standard ) to another server (2014 standard) and I have thought about restoring the database but the problem which I am running into is that users have been updating both databases for quite some time now. My question is if there is a tool or a magic wand I can use to make sure that the restore won't overwrite the existing data.Has anyone worked on this sort of issue and how it was resolved? Any help is highly appreciated.

    Kind of... RedGate Data Compare is a decent tool for this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 30, 2017 at 4:28 pm

    #1949018

    you can also use visual studio for data/schema comparison

  • June 30, 2017 at 7:33 pm

    #1949031

    Restore the database on the new server from the full backup.
    Establish log shipping. Make it often - every 1 or 3 minutes.
    Once Restore log has caught up with Backup log - disconnect all Application instances from the old database, switch to SINGLE USER mode, take the last log backup and wait until it's restored on the new server. 
    That last RESTORE must be WITH RECOVERY.
    While the last log backup is transferred you have time to reset your Application config files to connect to the new server. See if you need to restart Application instances.
    Once it's done the new copy of the database must be online with the latest data in it.
    Ready to use.
    Outage time - must be less than 1 minute, if the log backups are not too big.
    But you must have everything scripted, and test the scripts.

    _____________
    Code for TallyGenerator

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

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