May 25, 2020

Database Downgrade with SQL Server BACPAC

In our first image, we see compatibility_level as 140, which came in 2017. This can be confusing for “downgrade” operation. Changing this from 120 “compatibility_level” to 120 doesn’t mean you can move your database to a 2014 SQL Server.

As you can see in the first query, we see that our database is also in the 904 version, which is kept as “Internal Database Version”. This informs us that our database is on SQL Server 2019.

Internal Database Version
Internal Database Version

 

You cannot restore a SQL Server with a lower version than the current version. We can understand from this information that you need SQL Server Internal Database Version 904 (SQL Server 2019) and above.

With the Export Data-Tier Application feature, we can move on to how we can do this.

Situations we should be aware of

You have to be very careful with the words Extract and Export. Extract process gives you a Schema-based “.dacpac” file. Our transaction is “Export”. Here we take Data and Schema together. The extension is “.bacpac”.

You can comfortably process SQL Server 2014 and above. You have the possibility to experience different problems depending on the data types related to older versions.

This process does not carry the data of objects such as index, even if it carries your data held in the tables. After writing the scripts of the indexes, he wants to enable “Rebuild” and enable it. This creates the possibility of a long downtime in the Import phase.

Some situations I need to touch on

It is useful to get Full Backup before applying.

If you remove the users you defined under the database before exporting, it will reduce the error rate.

If you are using too many “Stored Procedure”, “Function”, “View” and in “Cross-Database Queryler Import” phase in which we access our other databases, it will fail.

Commands containing xp_cmdshell, sp_start_job processes will give an error during the Import Phase.

Let’s take a look at export and import transactions.

Our main goal is to carry the data in a practical way. With “Generate Script” feature, Stored Procedure etc. If we remove these objects from the database before we start the Export process after we receive the CREATE scripts of our objects, we will not have any problems related to them. After our import operations are completed successfully, we can quickly create these objects again on our new database by running this CREATE script file we created.

Let’s start our Export transactions. We will do it on the “AdventureWorks2017 OLPT” version that everyone can access. As you can see in the “Internal Database Version” picture, we will move this database from a server in SQL Server 2019 version to a server with version 782 (SQL Server 2014).

As the first transaction, we enter the “Tasks> Export Data-tier Application” transaction via the database.

Export Data-tier Application
Export Data-tier Application

 

On the “Export Settings” page, we are asked to specify a disk space to extract our “.bacpac” file. Let’s select the desired location from the “Browse” option.

Export Settings
Export Settings

 

“Select All” option is selected by default in the “Advanced” tab and we can select or remove the desired scheme from here. We leave it all selected here. Then click the “Next” button.

Advanced
Advanced

 

Finally, we can see all our settings on our summary screen. Let’s go to the next step by clicking the “Finish” button.

Summary
Export page “Summary”

 

We successfully export our database as seen on the “Results” page. Generally, there is not much problem at this stage. The part where the main events will develop begins on the Import side. Let’s close our page by clicking the “Close” button.

Results
Export page “Results”

 

The .bacpac file we extracted is 17MB, the size of our database is 336MB.

Now going to our other server where MS SQL Server 2014 is installed. We select our “Databases> Import Data-tier Application” option.

"Databases> Import Data-tier Application
“Databases> Import Data-tier Application

 

On the “Import Settings” page, we show our .bacpac file that we export from the “Browse” button. Then click the “Next” button.

Import Settings
Import Settings

 

On the “Database Settings” page, he asks where to keep the .mdf and .ldf files for the newly created database and with which name we want to create our database.

Database Settings
Database Settings

 

We expect the transfer to be completed by pressing the “Finish” button after checking our latest information before importing in the “Summary” page.

İmport page "Summary"
İmport page “Summary”

 

The “FULLTEXT INDEX” property failed because it is not active in the database. The procedure to be performed here will repeat the process by trying to detect and remove how many tables belonging to this feature in the existing database from the system tables, and then try to import first, then Export.

database İmport Error
database İmport Error

 

When I repeat the process, as you can see on the “Results” page, Data and indexes were created. Immediately after, Enable indexes to end the process.

İmport page "Results"
İmport page “Results”

 

When we check our new database, we can see that it works in 782 (SQL Server 2014) and 120 (SQL Server 2014) compatibility mode.

New database
New database

 

Finally, do not forget to take your backups and make sure your backups work while doing the operations here.

Leave a Reply

Your email address will not be published. Required fields are marked *