locked
Migrating from SQL 2005 Enterprise to SQL 2014 Standard Edition. RRS feed

  • Question

  • Hi ALL,

    We are Migrating from SQL 2005 Enterprise to SQL 2014 Standard Edition.

     SQL 2005 Ent Database is having Database Partition which is not supported in SQL 2014 STD. So I restored the A db's Backup on a New B db, removed the partition and took the back up of Non-partitioned db(B) and Restored it  on SQL 2014 Std (let me call it as C db) and its working fine.

    The problem here is we need to take diffrential/Transactional backup of A db everyday and apply it on the B db which resides on same instance (SQL 2005 Instance) and then take that differential backup of B and apply it on C(2014 Std Instance) until we go Live.Since taking whole backup and removing it partition and then restoring it on SQL 2014 takes atleast 3 to 4 hours of time.

    Is it possible to apply the transactional backup of a Partitioned database onto a Non-Partitioned DB?? Wont there be any schema changes in the Tran/diff backup files which we are trying to restore or Is there any alternate way to over come this problem . 

    Please help.Thanks in Advance


    Ramya

    Thursday, February 11, 2016 9:55 AM

Answers

  • Hello Ramya,

    A restore of a backup will re-create the origin database 1:1, so with partitioning. So using differential/transactional backups won't work in your scenario. You have to apply all DDL changes manuall on the new Server and copy over the new/changed data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 11, 2016 11:04 AM
  • The restore of log backup would not work because log backup might contain information about enterprise changes and when it will try to replay it on standard and will fail due to limitation

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP


    Thursday, February 11, 2016 11:06 AM

All replies

  • Hello Ramya,

    A restore of a backup will re-create the origin database 1:1, so with partitioning. So using differential/transactional backups won't work in your scenario. You have to apply all DDL changes manuall on the new Server and copy over the new/changed data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 11, 2016 11:04 AM
  • The restore of log backup would not work because log backup might contain information about enterprise changes and when it will try to replay it on standard and will fail due to limitation

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP


    Thursday, February 11, 2016 11:06 AM
  • Thanks for replying

    Ramya

    Tuesday, February 16, 2016 5:54 AM
  • Thanks for your reply

    Ramya

    Tuesday, February 16, 2016 5:55 AM