Downgrading SQL Server database and moving it to another computer

Question

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?

Answer 1

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.

Answer 2

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

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

Using bacpac file method

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

   Apexsql : Backward migration