Upgrading a SQL Server database to a newer version is one of the core duties of any DBA, and this holds true for practically any database system.
But what if this wasn't an upgrade? What if you receive a database (MDFs, LDFs, etc.) or a database backup file/s for a specific version of SQL Server, but you need to use it on a lower version of SQL Server? Or, suppose the database upgrade succeeds, but the application breaks, and none of your database rollback options work, including restoring from the database backups taken just prior to the upgrade. You may still have previous backups, but they don't contain the most recent data. In that case, finding a way to downgrade the database to the prior version might be your most preferred option. You can't simply attach the database (MDFs, LDFs, etc.) or restore that database backup there.
You can however still copy/restore the database to the SQL Server version it was copied from. Then use one of the export/import tools to copy the data from there to the target SQL Server. This obviously requires more work on your part, is complex and is a lengthier process. But it is still a solution that can get the job done, barring any compatibility issues.
Here, however, I want to discuss how to accomplish that using the SqlPackage.exe utility of the Data-tier applications (DAC) as I think it's more efficient and easier in comparison.
In a nutshell SqlPackage.exe is a command-line utility that allows you to export/extract an SQL Server database and optionally import into another SQL Server. Sqlpackage.exe is too a data export/import tool with additional features, and it uses the BCP utility (bcp.exe) behind the scene to copy data in and out of a database.
It essentially gives you a command line alternative to some of the APIs and functionalities of the Data-tier applications (DAC) that you may have seen in the SSMS.
Hold on, wait a second. if Sqlpackage.exe (supposedly) only offers a subset of functionality of DAC GUI in SSMS, and if I only need to do this once, why should even consider using this command line utility at all?
The short answer, generally you won't, until and unless you run into errors or issues where GUI is too rigid and inflexible to help you. Please see my notes on potential compatibility errors towards the end of this article.
The intended use case of Sqlpackage.exe is to migrate a database to another server, whether on-premise or in the cloud, or to upgrade the database to a higher version. However, in these cases the database backup/restore or attach/detach methods are much easier and more efficient, simply because in background SQL Server instance will automatically upgrade the internal structure of the database to match it's version. Therefore, I rarely use SqlPackage.exe for that purpose.
SqlPackage.exe has two modes or application types, either DACPAC or BACPAC. The DACPAC mode, by default, extracts only the database schemas without the data. On the other hand, BACPAC extracts database along with all the data.
For more information and download instructions to get the latest version, see:
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage
First I will describe the steps for using BACPAC method.
For this demo, I will copy the Northwind database from a SQL Server 2022 instance to a SQL Server 2016 instance.
And, Before you begin, ensure you have SqlPackage.exe installed on your machine or access to a server where it's available. You can usually find it in the "DAC\bin" directory of your SQL Server installation, such as "C:\Program Files (x86)\Microsoft SQL Server\<SQL Server Version>\DAC\bin".