Quick Question on 2019 to older version?

  • A client of ours is upgrading their database to SQL Server 2019.

    The edition of the SQL server that these database backups are currently being automatically restored to is SQL Server 2016.   I was wondering if anyone knew if its possible to restore a 2019 backups to a 2016 server without changing the compatibility level before its sent (also assuming the backup isnt compressed)?

    I don't have a 2019 server in my environment to test this with currently so thought I'd hit up you fine gentlemen.

  • You cannot restore a 2019 database to a 2016 server even if you're in the 2016 compatibility mode on the 2019 server.

    --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)
    Intro to Tally Tables and Functions

  • Thank you sir I appreciate it.

  • In fact, you cannot restore any newer-version SQL Server db to any older-version SQL instance, period.  Just does not work.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Well....there's always a way.  The way you are looking for here is exporting it as a bacpac and then importing it into the 2016 server.  It's not nearly as nice a process as the backup restore process you probably already have automated, but it is possible.

     

  • thebody wrote:

    Well....there's always a way.  The way you are looking for here is exporting it as a bacpac and then importing it into the 2016 server.  It's not nearly as nice a process as the backup restore process you probably already have automated, but it is possible.

    I've never tried that and haven't had the need to do so but thanks for the tip.  I didn't know such a thing would work.

    --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)
    Intro to Tally Tables and Functions

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

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