1

I have created database on my computer using SQL Server 2019, and now i want to move it to another computer that has SQL Server 2014 installed on it.

Do I have to download SQL Server 2014 and downgrade the database or there is another solution for it? What should I do?

3
  • What is the compatibility level of your database at 2019 server? Jan 30, 2020 at 12:19
  • 2
    The compatibility level of the database is not relevant--the version of the database and the compatibility level are two separate things. Compatibility level can be changed higher or lower at will, but version can only be upgraded. Compatibility level does not change the physical database. Jan 30, 2020 at 12:43

2 Answers 2

11

There's no such thing as downgradning the physical database. You would have to (use a tool that) script out your objects (CREATE commands) and the data (as INSERTs or some BCP files). And hope that you don't use anything on your 2019 that isn't available on 2014.

Such tools can be BACPAC, SSDT, "Generate Scripts" in SSMS.

4

While using the latest version of SQL Server or Using Azure SQL database/Azure SQL Managed Instance, you need to have a backout plan in case you need to go back to a lower version.

During my cloud migrations from on-prem, this is one of the most important lesson and i always ask these question before any planning.

Backup/Restore or detach /attach are not backward compatible which makes the process a bit tidious. This happens becasue the sql server upgrades your database automatically when these methods are used.The buildnumber associated with the file makes it possible to only be restored/attached on same or higher build.

Now the options you have (as mentioned above by Tibor):

  1. Generate script method : This will be a bit easier if the database in question is a micky mouse .

Generate Scripts method

  1. Using bacpac file : Export Data-tier Application. This is something i have always used via ssms or sqlpackage

Using bacpac file method

  1. Third party tools (No recommending but you do have options open)

    Apexsql : Backward migration

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.