Two weeks ago Microsoft released CU8 for Dynamics NAV 2013 R2.  This upgrade included the long-awaited Import and Export that is replacing the old FBK backup in the Classic Client.

There are two ways to do import and export.  One is through the Windows Client

ImportExport

and the other is with PowerShell commands Export-NAVData and Import-NAVData.  You can read all about this on the Microsoft Dynamics NAV Team Blog by clicking here.

Compared to the old Classic Client FBK backup this is very fast.  On the other hand the new import functionality is very strict on the database schema.  It needs to be excactly the same in the database used for the export and import.

I was in the process of upgrading a company from NAV 5.0 SP1 and had planned to add the company to an existing NAV 2013 R2 database wich already included a few companies.  This was not as easy as you might think.  Here are the issues that I encountered and how they were solved.

After I finished the upgrade I made sure that all objects in the upgraded database where identical to the destination database.  This I did by exporting all objects from the destination database to the upgraded database.  I used a merge tool on the exported text object file to make sure.

At this point I was not able to import the data file and asked for help from Microsoft.  Got all the help I needed and the first thing that I needed to make sure was that all the tables must be identical on a SQL level.  This was done with this SQL command.

[code lang=”sql”]select db1.[Object ID] "DB1 Table ID", db1.Name "DB1 Table Name", db1.Hash "DB1 Metadata Hash", db2.[Object ID] "DB2 Table ID", db2.Name "DB2 Table Name", db2.Hash "DB2 Metadata Hash"
from [Destination Database].[dbo].[Object Metadata Snapshot] db1
full outer join [Source Database].[dbo].[Object Metadata Snapshot] db2 on db1.[Object ID] = db2.[Object ID][/code]

I needed to make sure that the “DB1 Metadata Hash” what identical to “DB2 Metadata Hash” for all tables.  This was not true even if all objects where identical.  After recompiling all tables in both databases I saw that I now had identical hashes.

This was not enough.  Since I upgraded the source database from an earlier NAV version the database still held all the old virtual table snapshots.  We needed to remove them with this SQL command.

[code lang=”sql”]delete from [Source Database].[dbo].[Object Metadata Snapshot]
where [Object ID] in
(2000000007, 2000000026, 2000000049, 2000000101, 2000000102, 2000000103, 2000000020, 2000000055, 2000000009, 2000000029, 2000000038, 2000000058, 2000000041, 2000000028, 2000000063, 2000000022, 2000000048, 2000000040, 2000000043, 2000000044, 2000000039, 2000000045)
[/code]

After this I successfully exported and imported the upgraded company into the destination database.  One more thing that I saw is that I needed to restart other service instances in order to see the newly imported company.

This new functionality opens a lot of possibilities for developement and testing.  We can now easily with PowerShell scripts create a backup of our live company data and import them into our test and/or developement environment.

In both the Windows Client and in PowerShell it is possible to copy companies, rename and delete.  I know that the import process takes a longer time than the export process.  I tried the export process on a 500GB database and it took 2 hours and 27 minutes.  In the Classic Client this would have taken days.

I expect to see this functionality improve in future releases and also expect Microsoft or some of the community leaders to publish scripts to automate a data refresh in a testing environment.

5 thoughts on “Importing and Exporting Data in Microsoft Dynamics NAV 2013 R2 CU 8

  1. Jens Glathe says:

    Hi Gunnar,

    thank you for sharing your experience. What you write sounds like any real-world tests have been omitted by Microsoft. One absolutely real-world example is to back up companies and delete them from the production database, and restore them when they are needed (mostly for reference). The chances that the database schema of the backup is exactly the same as in the production database (after a few weeks/months/years) is practically zero. And this scenario was (as long as no non-empty table fields were deleted between backup and restore) absolutely no problem with the .fbk, albeit slow. The fact that even version changes (and upgrade artifacts) prevent the restore from being successful is proof of this. This is the same with SQL backup files… only upwardly compatible. So preparing a database on a server that has a higher build number than the production server will void your effort.

    with best regards

    Jens

    1. You are correct Jens.
      I will go over this with Microsoft and suggest some improvements. Without a doubt we will see improvements from this first version.

  2. Jeffrey Poen says:

    Thanks!!!!!!! This issue toke me hours

  3. KBS says:

    nice posting thanks for given information

Leave a Reply

%d bloggers like this: