How to export a Dynamics NAV database on SQL Server 2016
If you use SQL Server 2016 for your Dynamics NAV database, and you need to export a database to the .bacpac format, then please make sure that you are running the latest update (August 2016 update or later) of the Microsoft SQL Server Data-Tier Application Framework (DacFx) or SQL Server Management Studio (SSMS).
If you are interested in why we recommend this, here is the story of how we found a bug in Dynamics NAV, analyzed the impact on customers and partners, and then finally found out that it wasn’t a bug in Dynamics NAV, but in the tools around SQL Server. And the story even has a happy ending. And some Windows PowerShell bonus info 🙂
During a manual test outside our test lab, we recently discovered that the Dynamics NAV 2017 demo database couldn’t be exported to a .bacpac file:
"c:\Program Files\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe" /Action:Export /TargetFile:nav-test.bacpac /SourceServerName:10.0.0.10 /SourceDatabaseName:"Demo Database NAV (10-0)" /SourceUser:sa /SourcePassword:blablabla Connecting to database 'Demo Database NAV (10-0)' on server '10.0.0.10'. Extracting schema Extracting schema from database Resolving references in schema model Validating schema model Validating schema model for data package Validating schema *** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package. Error SQL71564: View: [dbo].[CRONUS International Ltd_$Service Shipment Buffer$VSIFT$0] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near "CRONUS International Ltd_$Service Shipment Buffer$VSIFT$0". Error SQL71564: View: [dbo].[CRONUS International Ltd_$Calendar Entry$VSIFT$0] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near "CRONUS International Ltd_$Calendar Entry$VSIFT$0". ...
By zooming in on one of the error rows, such as:
Error SQL71564: View: [dbo].[CRONUS International Ltd_$Service Shipment Buffer$VSIFT$0] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near "CRONUS International Ltd_$Service Shipment Buffer$VSIFT$0".
we saw that the view which contained the errors was used for a SIFT index.
Querying the view from SSMS gave no errors. This indicated that from the point of view (pun intended!) of SQL Server and therefore also of Dynamics NAV, the view was healthy and SIFT indexes were working in Dynamics NAV.
And maybe the problem might be hidden in the .bacpac export. To test this hypothesis, we created an empty database with one table and one view by using the same syntax as the view that was giving us trouble:
IF OBJECT_ID(N'id_v', N'V') IS NOT NULL DROP VIEW id_v GO DROP TABLE IF EXISTS id GO CREATE TABLE id(n int) GO CREATE VIEW id_v AS SELECT "abc".n FROM dbo.id "abc" GO
Exporting this database by using sqlpackage still triggered the error:
"c:\Program Files\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe" /Action:Export /TargetFile:nav-test.bacpac /SourceServerName:10.0.0.10 /SourceDatabaseName:dacpac-test /SourceUser:sa /SourcePassword:blablabla Connecting to database 'dacpac-test' on server '10.0.0.10'. Extracting schema Extracting schema from database Resolving references in schema model Validating schema model Validating schema model for data package Validating schema *** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package. Error SQL71564: View: [dbo].[id2_v] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near "abc".
It seemed like sqlpackage had a problem with quoted identifiers. We contacted the SQL Server team responsible for DacFx to file a bug, and they immediately confirmed that:
- this was a bug, and
- it had been fixed in the July update of DacFx: https://blogs.msdn.microsoft.com/ssdt/2016/06/30/sql-server-data-tools-july-update-2/ (actually, it didn’t make it into the July version, but in the August update.)
So, as stated in the introduction, you should use the DacFx msi installer from the August update (or later).
Bonus info:
To make sure that your environment is always updated with the latest version of DacFx, try using the PowerShell package manager PowerShellGet at:
https://msdn.microsoft.com/en-us/powershell/reference/5.1/powershellget/powershellget
Then, from today and going forward, you can run this to get and install the latest version of DacFx:
Register-PackageSource -Name NuGet -Location https://www.nuget.org/api/v2 -Provider NuGet -Verbose Install-Package -Name Microsoft.SqlServer.DacFx.x64 -MinimumVersion 130.3485.1 -ProviderName NuGet -Force