I have been struggling with this a few times, and I was sure that I succeeded earlier when trying to import a .navdata file into an empty SQL database, so this time I’m going to write a short post about it.
Looks like it is really quite simple.
If you have a .navdata file that contains application, application data, global data and some company data as well, then you can import this with PowerShell directly into an empty SQL Server database.
First you need to create the empty database and you can do this from Microsoft SQL Server Management Studio or the Azure Portal if you are on Azure SQL.
Then open up PowerShell and import your NAV-modules. I use the Import-NAVModules cmdlet from Cloud.Ready.Software.NAV, but you can also use…
Import-module “C:\Program Files (x86)\Microsoft Dynamics NAV\11.0.20783\Service\Microsoft.Dynamics.Nav.Management.dll”
Then run Import-NAVData…
Import-NAVData -DatabaseServer <SQL server name> -DatabaseName <Database name> -ApplicationDatabaseServer <SQL server name> -ApplicationDatabaseName <Database name> -FilePath “C:\temp\backup.navdata” -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -CommitPerTable -Force
Note this example is a single tenant database. If you are using SQL server instances you can type mysqlserver\myinstance under -DatabaseServer and -AppliationDatabaseServer.
Does it work on Azure SQL? Yes it does…
Import-NAVData -DatabaseServer $azuresql -DatabaseName $azuresqldb -DatabaseCredentials $azuresqlcred -ApplicationDatabaseServer $azuresql -ApplicationDatabaseName $azuresqldb -ApplicationDatabaseCredentials $azuresqlcred -FilePath “C:\temp\backup.navdata” -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -CommitPerTable -Force
If you omit the -Force parameter you will get this…
Import-NAVData : System table $ndo$dbproperty is missing or database MyDatabase is not a Dynamics NAV application database
.
At line:1 char:1
+ Import-NAVData -DatabaseServer $azuresql -DatabaseName $azuresqldb -D …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (0:Int32) [Import-NAVData], NavSqlException
+ FullyQualifiedErrorId : agidonsql2.database.windows.net\nhtest2,Microsoft.Dynamics.Nav.Management.Cmdlets.Import
NavData
EDIT: I noticed that the actual company data was not imported with the above commands, not sure why. But if you experience the same you can always run the commands again without the -IncludeApplication, -IncludeApplicationData and -IncludeGlobalData parameters.
Also I got this message on Azure, when the Import-NAVData command finished…
Import-NAVData : Error while trying to set the collation for database nhtest2 containing tenant default. The error was:
The following SQL error was unexpected:
The database could not be exclusively locked to perform the operation.
ALTER DATABASE failed. The default collation of database ‘nhtest2’ cannot be set to Danish_Greenlandic_100_CS_AS.
I tried deleting the database and recreating it from the Azure Portal specifying ‘Danish_Greenlandic_100_CS_AS’ as collation. After that I was able to restore everything to the Azure SQL database.