Is it possible to restore the 2019 DB backup to 2012 DB with the same name?

Question

Hi All,

We are planning to perform SQL server migration from the 2012 clustered environment to the 2019 clustered environment. Please refer to the below scenario.

Scenario 1:
Let assume, after migration DBs are working fine for 1 hour. However, after 1 hour it is required to roll back to DBs on SQL server 2019 to SQL server 2012 due to an issue.
We are required to overwrite old SQL server 2012 DBs. DB sizes are around 500GB. Those DBs are heavily used with a lot of records updates. Since this is business-critical DBs, writing scripts and capturing change data, and inserting it into the old 2012 environment is not possible. Because it will take longer time.

So, Is it possible to restore the 2019 DB backup to 2012 DB with the same name?

Answer 1

Hi @Chamali Liyanage ,

> Is it possible to restore the 2019 DB backup to 2012 DB with the same name?

No, we can’t restore a SQL Server backup to a lower version.

I test this on my environment.

DB2012----compatibility level 110(2012). I got the below error when I back the database on SQL 2019 and restore it on SQL 2012.

SQL2012DB--- compatibility level 150(2019). I got the below error when I back the database on SQL 2019 and restore it on SQL 2012.

I suggest you using below methods to migrate database to lower version.

1. Using SSMS Generate Scripts Wizard.

In the Advanced Scripting Options dialog box,
•set Script for Server Version to SQL Server 2012 (or whatever version you want)
•under the Table/View Options, set Script Triggers, Script Indexes and Script Primary Keys to True
•and set Types of data to script to Schema and Data - this last option is key because this is what generates the data per table.

Then run the generated scripts on SQL 2012 instance.

Refer to this blog to get more.

For large databases, around 1GB or more, if this approach does not work, then you should look at using SSIS to migrate the database.

2... You can use functionality called Export Data-Tier Application which generates .bacpac file consisting database schema and data.
On destination server, you can use Import Data-Tier Application option which creates and populates new database from pre-created .bacpac file.

Refer to MS document Using the Export Data-tier Application Wizard.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Probably not. When you move your DB to SQL 2019 are you changing the compatibility level of the DB as well? If so then you cannot go back. If you don't then in theory it should work but there could be changes made that would prevent it.

But I suspect you won't be allowed to. To do what you want you'll have to backup the DB again in SQL2019. This backup is going to be tied to 2019 I wager via metadata. When you try to restore on SQL 2012 it is going to see that a newer SQL version was used for the backup and it'll fail the call. That's just a guess but you should be able to easily test this by taking a backup of your SQL2019 instance and trying to restore it.