none
SQL server data migration of production data

    Question

  • I have db1, db2(empty)

    db1 are both production database(data is live and inserted into these db)

    I am export db1 data to db2, currently I have a application running that will update any changes from db1 to db2.

    In order to reduce the downtime, the strategy is to migrate db1 data to db2 while db1 is currently live.

    Let's say at 12.12pm , start import/export wizard

    12.34pm , finish import/export wizard

    db2 will contain db1's data at 12.12pm?

    Wednesday, November 06, 2013 7:26 AM

Answers


  • Let's say at 12.12pm , start import/export wizard

    12.34pm , finish import/export wizard

    db2 will contain db1's data at 12.12pm?

    Not sure you are talking about table or database .Please clear

    No its not guaranteed that data count will match in both tables( i assume) .I would like you to perform this activity during application downtime.Then when records in table 2.Now both table are same  records now create and update trigger on table 1 which will (on update) insert same record in table 2.

    For creating a sync between two different databases use following option as per your requirement

    1. Log shipping

    2. Mirroring

    3 Replication.

    >>Let suppose, if start backup at 12:00 PM and it is completed at 12:12 PM. Than if you restore the database it will contain data till 12:12 PM

    Neha only transaction committed before or at 12:12 will be there in backup.Transaction/changes started at 12:10 but not committed at 12:12 will not be present in 12:12 backup.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, November 06, 2013 12:45 PM
  • create a SSIS package with database transfer task and schedule the same in Server Agent Service

    Ramesh Babu Vavilla MCTS,MSBI

    Wednesday, November 06, 2013 12:47 PM

All replies

  • I assume import/export you are reffering to backup restore. Also, You should use backup and restore in this scenrio

    Let suppose, if start backup at 12:00 PM and it is completed at 12:12 PM. Than if you restore the database it will contain data till 12:12 PM


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Wednesday, November 06, 2013 9:02 AM

  • Let's say at 12.12pm , start import/export wizard

    12.34pm , finish import/export wizard

    db2 will contain db1's data at 12.12pm?

    Not sure you are talking about table or database .Please clear

    No its not guaranteed that data count will match in both tables( i assume) .I would like you to perform this activity during application downtime.Then when records in table 2.Now both table are same  records now create and update trigger on table 1 which will (on update) insert same record in table 2.

    For creating a sync between two different databases use following option as per your requirement

    1. Log shipping

    2. Mirroring

    3 Replication.

    >>Let suppose, if start backup at 12:00 PM and it is completed at 12:12 PM. Than if you restore the database it will contain data till 12:12 PM

    Neha only transaction committed before or at 12:12 will be there in backup.Transaction/changes started at 12:10 but not committed at 12:12 will not be present in 12:12 backup.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, November 06, 2013 12:45 PM
  • create a SSIS package with database transfer task and schedule the same in Server Agent Service

    Ramesh Babu Vavilla MCTS,MSBI

    Wednesday, November 06, 2013 12:47 PM