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 "DACbin" directory of your SQL Server installation, such as "C:Program Files (x86)Microsoft SQL Server<SQL Server Version>DACbin".
BACPAC (bacpac export) Method:
Open a command prompt (cmd.exe) or terminal window then switch to the directory where sqlpackage.exe is located:
Export the Database
Execute the following command to export a SQL Server database to a .bacpac file. Replace the parameter values with your specific values:
SqlPackage.exe ^ /a:Export ^ /ssn:localhost ^ /sdn:Northwind ^ /tf:Northwind.bacpac ^ /SourceEncryptConnection:True ^ /SourceTrustServerCertificate:True ^
- /ssn:<ServerName>: Source SQL Server instance name.
- /sdn:<DatabaseName>: Source database name
- /tf:<TargetFile.bacpac>: Name of the .bacpac file where the exported data will be saved.
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage
Import the Database Into Target SQL Instance
Execute the following command to import a SQL Server database from a .bacpac file. Please remember to update the parameter values with your specific values:
.SqlPackage ^ /a:Import ^ /sf:"Northwind.bacpac" ^ /tsn:localhostsql2016 ^ /tdn:"Northwind" ^ /TargetEncryptConnection:True ^ /TargetTrustServerCertificate:True
- /tsn:<TargetServerName>: Target SQL Server instance name.
- /tdn:<TargetDatabaseName>: Target database name to import the data into.
- /sf:<SourceFile.bacpac>: The source .bacpac file from which to import the data.
DACPAC (dacpac export) Method:
Execute the following command to extract a SQL Server database to a .dacpac file. Please don't forget to update the parameter values with your specific values:
.SqlPackage ^ /a:Extract ^ /tf:"Northwind.dacpac" ^ /ssn:localhostSQL2022AG01 ^ /sdn:"Northwind" ^ /SourceEncryptConnection:True ^ /SourceTrustServerCertificate:True ^ /p:VerifyExtraction=False ^ /p:ExtractAllTableData=True
Notice the parameter /p:ExtractAllTableData=True to also export the data. This is required because by default dacpac method would only extract schema definitions.
Publish (Import) the Database Into Target Instance
Execute the following command to publish a SQL Server database from a .dacpac file. Please don't forget to update the parameter values with your specific values:
.SqlPackage ^ /a:Publish ^ /sf:"northwind.dacpac" ^ /tsn:"localhostSQL2016AG01" ^ /tdn:"northwind" ^ /TargetEncryptConnection:True ^ /TargetTrustServerCertificate:True ^ /p:AllowIncompatiblePlatform=True
Compatibility Errors:
I have found SqlPackage.exe far more efficient in terms for overall performance and ease of use, compare to other options. A major drawback of it though is the potential compatibility issues you may run into that may prevent you from using this tool. This limitation though is due to the underlying Data-tier applications APIs that it uses therefore you would run into same issues even when you use the Data-tier applications feature of SSMS. In fact often the main impetus for using sqlpackage.exe is because it offers great flexibility to modify and customize it's behavior by way of setting parameter values and xml config file, something that SSMS cannot do due to its GUI nature. Unfortunately, the list of parameters is too big for me to cover in a single blog article.
Often, the compatibility issues arise because the source database you are trying to downgrade is now using features, configuration and syntax that are not available in the older, target SQL Server version. For example in SQL Server 2022 you can have ORDERED COLUMNSTORE INDEXES. Trying to downgrade a database with ORDERED COLUMNSTORE INDEXES, to say even SQL Server 2019, will throw an error. Here is an actual error I received when I first tried using the WideWorldImportersDW database for this demo.
Initializing deployment
*** A project which specifies SQL Server 2022 or Azure SQL Database Managed Instance as the target platform may experience compatibility issues with SQL Server 2016.
Verifying deployment plan
Analyzing deployment plan
Importing package schema and data into database
Updating database
*** Error importing database:Could not import package.
Warning SQL0: A project which specifies SQL Server 2022 or Azure SQL Database Managed Instance as the target platform may experience compatibility issues with SQL Server 2016.
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'ORDER'.
Error SQL72045: Script execution error. The executed script:
CREATE CLUSTERED INDEX [CCX_Fact_Order]
ON [Fact].[Order]([Order Key])
ON [PS_Date] ([Order Date Key]);
CREATE CLUSTERED COLUMNSTORE INDEX [CCX_Fact_Order]
ON [Fact].[Order] ORDER([Order Key]) WITH (DROP_EXISTING = ON)
ON [PS_Date] ([Order Date Key]);
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 319, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Error SQL72045: Script execution error. The executed script:
CREATE CLUSTERED INDEX [CCX_Fact_Order]
ON [Fact].[Order]([Order Key])
ON [PS_Date] ([Order Date Key]);
CREATE CLUSTERED COLUMNSTORE INDEX [CCX_Fact_Order]
ON [Fact].[Order] ORDER([Order Key]) WITH (DROP_EXISTING = ON)
ON [PS_Date] ([Order Date Key]);
Time elapsed 0:00:07.54.
Often you are able to get around such errors by either using one of the parameter switches, or removing the offending object first, provided it won't cause an actual data loss, then add/create it back. I decided not to go that route here and instead switched my demo to use the good old Northwind sample database.
Conclusion
Downgrading a SQL Server database version is a complex task that should be approached with caution. It's essential to plan and test the process thoroughly in a controlled environment before attempting it on a production database.